July 11, 2012

Pivot Multiple Columns in sql server


MY table name  product1

ID    Amount      Date
----
1    300     02-02-2010 00:00
 2    400     02-02-2009 00:00
 3    200     02-02-2011 00:00
 4    300     22-02-2010 00:00
 5    400     12-02-2009 00:00
 6    500     22-02-2009 00:00
 7    600     02-02-2006 00:00
 8    700     02-07-2012 00:00
 9    500     08-02-2012 00:00
10    800     09-02-2011 00:00
11    500     06-02-2010 00:00
12    600     01-02-2011 00:00
13    300     02-02-2019 00:00

Desired output:
   Y1          Y2            Y3 ...........
 sum(amount)   sum(amount)   sum(amount)
What is an approach, where Y1 is the year part of the date, such that the result column would be the following?
YEAR       2006   2009    2010   2011   2012
-   ---------
Total         600   1300     800   1900   1200

 Create procedure [dbo].[pivot]  
 as  
 begin  
 DECLARE @Years nvarchar(max)  
 SELECT @Years = STUFF(  
 (  
 select distinct ',[' + cast(Year([Dates]) as nvarchar(4)) + ']'  
 from Product1  
 for xml path('')  
 ),  
 1,1,'')  
 DECLARE @SQL nvarchar(max)  
 SELECT @SQL = N'  
 select * from ( select amount, year([Dates]) as [y] from product1 ) Data  
 PIVOT ( sum(Amount) FOR [y] IN ( ' + @Years + ' ) ) PivotTable  
 UNION  
 select * from ( select units, year([Dates]) as [y] from product1 ) Data  
 PIVOT ( sum(units) FOR [y] IN ( ' + @Years + ' ) ) PivotTable  
 '  
 EXECUTE (@SQL)  
 end