Using multiple aggregate function in dynamic PIVOT query in SQL Server

Last Reply one year ago By dharmendr

Posted one year ago

i have use below query ..but that query does not work on dynamically. refer by ASPFORUMS site ...i have need to dynamically run query

DECLARE @PivotExample AS  TABLE
(
    MONTH VARCHAR(15),
    YEAR VARCHAR(4),
    PIECES VARCHAR(10),
    AMOUNT VARCHAR(10)
)
INSERT INTO @PivotExample VALUES
        ('Jan','2013','5','500'),
        ('Jan','2014','15','2500'),
        ('Feb','2013','2','300'),
        ('Dec','2013','10','400'),
        ('Dec','2014','40','4000')
SELECT  YEAR AS Year
        ,MAX(JanPcs) AS JanPcs,MAX(JanAmt) AS JanAmt
        ,MAX(FebPcs) AS FebPcs,MAX(FebAmt) AS FebAmt
        ,MAX(MarPcs) AS MarPcs,MAX(MarAmt) AS MarAmt
        ,MAX(AprilPcs) AS AprilPcs,MAX(AprilAmt) AS AprilAmt
        ,MAX(MayPcs) AS MayPcs,MAX(MayAmt) AS MayAmt
        ,MAX(JunePcs) AS JunePcs,MAX(JuneAmt) AS JuneAmt
        ,MAX(JulyPcs) AS JulyPcs,MAX(JulyAmt) AS JulyAmt
        ,MAX(AugPcs) AS AugPcs,MAX(AugAmt) AS AugAmt
        ,MAX(SepPcs) AS SepPcs,MAX(SepAmt) AS SepAmt
        ,MAX(OctPcs) AS OctPcs,MAX(OctAmt) AS OctAmt
        ,MAX(NovPcs) AS NovPcs,MAX(NovAmt) AS NovAmt
        ,MAX(DecPcs) AS DecPcs,MAX(DecAmt) AS DecAmt
FROM (SELECT YEAR,
             MONTH+'Pcs' AS MonthPcs,
             MONTH+'Amt' AS MonthAmt,
             MAX(PIECES) AS PIECES,
             MAX(AMOUNT) AS AMOUNT
      FROM @PivotExample
      GROUP BY YEAR, MONTH) AS T
      PIVOT
     (MAX(PIECES) FOR MonthPcs IN
     (JanPcs,FebPcs,MarPcs,AprilPcs,MayPcs,JunePcs,JulyPcs,AugPcs,SepPcs,OctPcs,NovPcs,DecPcs)) AS P1
      PIVOT
     (MAX(AMOUNT) FOR MonthAmt IN
     (JanAmt,FebAmt,MarAmt,AprilAmt,MayAmt,JuneAmt,JulyAmt,AugAmt,SepAmt,OctAmt,NovAmt,DecAmt)) AS P2
GROUP BY YEAR;

 below query does not work it has made by me . ..so please give to soluation on below query

DECLARE @cols AS NVARCHAR(MAX), @cols1 AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(InvoiceNo) 
                    from FinalSupplyReconciliation
                    group by InvoiceNo
                    order by InvoiceNo
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols1 = STUFF((SELECT ',' + QUOTENAME(InvoiceNo)
                    from FinalSupplyReconciliation
                    group by InvoiceNo
                    order by InvoiceNo
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT ItemDescr,' + @cols + ',' + @cols1 + ' from (select ItemDescr, InvoiceNo, InvoiceQty,Amt from FinalSupplyReconciliation) x

            pivot 
            (
                sum(InvoiceQty)
                for InvoiceNo in (' + @cols + ')
            ) as n

			pivot 
            (
                sum(Amt)
                for InvoiceNo in (' + @cols1 + ')
            ) as p'

			execute(@query);

i want desired output like this....

itemdescription          INo1Qty INo1Amt   INo2Qty  INo2Amt  

25 x 6 mm G.I. Strip   11           1241       24            365

25 x 3 mm Copper       25           4578       14            145

Posted one year ago

Hivishalkal,

Check this below example.

SQL

CREATE TABLE FinalSupplyReconciliation(ItemDescr varchar(5),InvoiceNo varchar(3),PORate int,Amt int)

INSERT INTO FinalSupplyReconciliation VALUES('DD1','A',1000,10)
INSERT INTO FinalSupplyReconciliation VALUES('DD1','B',2000,20)
INSERT INTO FinalSupplyReconciliation VALUES('DD1','C',3000,30)
INSERT INTO FinalSupplyReconciliation VALUES('DD1','A',4000,40)
INSERT INTO FinalSupplyReconciliation VALUES('DD1','B',5000,50)
INSERT INTO FinalSupplyReconciliation VALUES('DD1','C',6000,60)
INSERT INTO FinalSupplyReconciliation VALUES('DD2','A',7000,70)
INSERT INTO FinalSupplyReconciliation VALUES('DD2','B',8000,80)
INSERT INTO FinalSupplyReconciliation VALUES('DD2','C',9000,90)
INSERT INTO FinalSupplyReconciliation VALUES('DD2','A',10000,100)
INSERT INTO FinalSupplyReconciliation VALUES('DD2','B',11000,110)
INSERT INTO FinalSupplyReconciliation VALUES('DD2','C',1200,120)

DECLARE @cols AS NVARCHAR(MAX),@query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(InvoiceNo+'_'+col) 
                    from FinalSupplyReconciliation t
                    cross apply
                    (
                        select 'PORate', 1 union all
                        select 'Amt', 2
                    ) c (col, so)
                    group by col, so, InvoiceNo
                    order by  InvoiceNo, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ItemDescr,' + @cols + ' 
            from 
            (
                select ItemDescr, 
                    col = InvoiceNo+''_''+col, 
                    value
                from FinalSupplyReconciliation t
                cross apply
                (
                    select ''PORate'', PORate union all
                    select ''Amt'', Amt
                ) c (col, value)
            ) x
            pivot 
            (
                SUM(value)
                for col in (' + @cols + ')
            ) p '

execute sp_executesql @query;

Output

ItemDescr A_PORate A_Amt B_PORate B_Amt C_PORate C_Amt
DD1 5000 50 7000 70 9000 90
DD2 17000 170 19000 190 10200 210