Convert Rows To Columns and Calculate Sum using dynamic Pivot query in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

i want to convert row into column and calculate sum, want result like this.

item Name 01-05-2019 01-05-2019 01-05-2019 01-05-2019
A 4 4 3 1
B 1 2 4 1
c 4 3 2 2

i have two table one table is Product(col:ItemCode,Name) and second table is Produce (ID,ItemCode,Prdqty).

please guide me ,i want above result date wise item produce quantity ..thanks

i tried this but not getting result giving error,here store procedure

USE [SilverProduction]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Pivot_Item1]
@StartDate date,
@Enddate   date
AS
BEGIN
    SET NOCOUNT ON;
SELECT DISTINCT EntryDate INTO #Date
FROM Probale
WHERE     (EntryDate BETWEEN @StartDate AND @Enddate)
ORDER BY EntryDate

DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, EntryDate), 112)
                + ']','[' + CONVERT(varchar,DATEPART(DAY, EntryDate), 112) + ']')
FROM    #Dates222
ORDER BY EntryDate
DECLARE  @qry NVARCHAR(4000) =
    N'SELECT * 
    FROM (SELECT ItemMasterFile.Codeitem,ItemMasterFile.Descriptionitem,Probale.prdqty, 
    DATEPART(DAY, Probale.EntryDate)as DDate
    FROM Probale  Inner Join ItemMasterFile on Probale.Codeitem=ItemMasterFile.Codeitem )prd
    PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat 
    '
EXEC(@qry) 
END

PLease guide thanks

Posted 3 months ago Modified on 3 months ago

Hi akhter,

Refer below sample query.

SQL

CREATE TABLE #tbl1(ItemCode INT, Name VARCHAR(20))
INSERT INTO #tbl1 VALUES(1,'A')
INSERT INTO #tbl1 VALUES(2,'B')
INSERT INTO #tbl1 VALUES(3,'C')
INSERT INTO #tbl1 VALUES(4,'D')
CREATE TABLE #tbl2 (ItemCode INT, Date DATETIME,Itemqty INT)
INSERT INTO #tbl2 VALUES(1,'2019-05-01',10)
INSERT INTO #tbl2 VALUES(1,'2019-05-01',20)
INSERT INTO #tbl2 VALUES(1,'2019-05-01',30)
INSERT INTO #tbl2 VALUES(1,'2019-05-01',40)
  
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Date) from #tbl2  as t1
inner join #tbl1 as t2 on t1.ItemCode=t2.ItemCode
--WHERE Date BETWEEN '2019-05-01' AND '2019-05-03'
GROUP BY Date ORDER BY Date FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'),1,1,'')
set @query = ';WITH CTE AS(
                    select tda.Itemqty,tda.Date,dts.Name, dts.ItemCode
                    from #tbl1 as dts
                    inner join #tbl2 tda on tda.ItemCode = dts.ItemCode
                    WHERE Date BETWEEN ''2019-05-01'' AND ''2019-05-03''
                )
                SELECT SUM(Itemqty) Itemqty,Name,' + @cols + ' from CTE   
                pivot(max(ItemCode) for Date in (' + @cols + ')) p
                GROUP BY Name,' + @cols + '
                 '        
execute(@query);
DROP TABLE #tbl1
DROP TABLE #tbl2