Pivot table with Dynamic Columns

    Pivot table with Dynamic Columns: Cases come when we need to reorganize and summarize selected columns and rows of data in a database table to obtain a desired report, and we then come to the concept of PIVOT. It is easy to PIVOT a table if the values are known in advance which need to be converted into Columns.

     But if we are required to PIVOT a table on the column whose values are dynamic, then it takes two steps to do so, and can be done by dynamic query.

Let's say we have a table ClientMaster as :

ClientCode
ProductID
Years
C001
P002
2001
C001
P001
2001
C001
P001
2002
C001
P004
2002
C002
P003
2001
C002
P002
2001
C002
P002
2002
C002
P003
2002
C002
P001
2002
C002
P004
2002

And now we like to represent the number of counts of products for different clients in different years as below :

ClientCode
2001
2002
C001
2
2
C002
2
4

Here remember that Years can vary and can be of any number. So now we will do :

1.  Make a comma separeted string of all possible values.

DECLARE @columns VARCHAR(8000)

SELECT  @columns = ISNULL(@columns +',['+CAST(Years AS VARCHAR)+']' , '['+ CAST(Years AS VARCHAR)+']')

FROM    ClientMaster (NOLOCK)

GROUP BY Years

2. Now write a query to PIVOT the table.

DECLARE @query VARCHAR(8000)

SET @query = 'SELECT * FROM 

                  (SELECT ClientCode, ProductID, Years

                   FROM ClientMaster (NOLOCK) 

                  ) S

                   PIVOT

                     (

                        COUNT(ProductID)

                        FOR Years

                        IN (' + @columns + ')

                      ) AS P'




 EXECUTE(@query)

 And here you are done....



 Reference: Govind Badkur(http://sqlserver20.blogspot.com)