Use multiple aggregate columns in pivot query in SQL Server

Hi Faizal,

I have created one sample that full-fill your requirement.

SQL

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;

Output

Hope this will help you.