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