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 :

create
table #tmpBeforePivot(PolicyKey int,[ValueDate] datetime,Amount  decimal(18,2))
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 .

          SELECT
                 PolicyKey,    
                 [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                  
           FROM
                 (
                    SELECT  [PolicyKey],           
                    month([ValueDate]) as ValueDateMonth,            
                    Amount         
                    FROM #tmpBeforePivot                   
                  ) p
                     PIVOT
                             (
                               sum(Amount) 
                               FOR ValueDateMonth IN
                               ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
                             ) 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