Monday, May 3, 2010

Pivot in SQL Server 2005

A pivot is a  method of grouping and displaying   data by means of aggregating values -
in  simple words , present one table-valued expression into another table.
Let see what that means in the example below.

Table #tmpBeforePivot representing table of transactions ,
the purpose is to get the transactions summary by each month of year in one row for
each policy .
You could use the case statement but within sql 2005 you got the pivot statement .

We will fill #tmpBeforePivot with dummy data :

table #tmpBeforePivot(PolicyKey int,[ValueDate] datetime,Amount  decimal
insert into #tmpBeforePivot(PolicyKey,[ValueDate],Amount) values(1,'2009-01-23',1243.00)

     and so on ... 

And now for the pivot ....

For each month declare in select statement ,column that represent month of year .
The alias such as "Jan","Feb","March"… is for your discretion but columns within [] should
be the same as excepted key in our example Month of year from 1 to 12 .

                 [1]  AS Jan, 

                 [2]  AS Feb,
                 [3]  AS March,
                                  [4]  AS April,    
                 [5]  AS May, 

                 [6]  AS June,
                 [7]  AS Jule,
                 [8]    AS August,
                 [9]  AS September,
                 [10] AS November,

                 [11] AS October,
                 [12] AS December                  
                    SELECT  [PolicyKey],           
as ValueDateMonth,            
                    FROM #tmpBeforePivot                   

            ) p
                               FOR ValueDateMonth IN
                             ) AS pvt
                  ORDER BY PolicyKey 

 Thats the result view for example above 

Very simple and usefull feature .
For more information look at msdn : Using PIVOT and UNPIVOT