Using SQL Server 2005/2008 Pivot on Unknown Number

2005/2008, the Pivot relationnal operator is used to convert rows into columns data. This feature is frequently used in reports and is pretty easy to work with.

Available in SQL Server 2005/2008, the Pivot relationnal operator is used to convert rows into columns data. This feature is frequently used in reports and is pretty easy to work with.

Here is a example :

We have a table Product, if we execute this simple query :

SELECT productId,_year,amount
FROM Products

We have this result :

productId   _year   amount
124         2001    125
125         2001    454
126         2001    75
127         2002    256
128         2004    457
129         2004    585
130         2002    142
131         2002    785
132         2005    452
133         2005    864
134         2005    762
135         2004    425
136         2003    452
137         2003    1024
138         2003    575

Now we are going to use PIVOT to have the years as columns and for a productId, the sum of amounts for 2001 and 2003 :

SELECT * FROM
(
    SELECT productId,_year,amount
    FROM Products
)t
PIVOT (SUM(amount) FOR _year
IN ([2001],[2003])) AS pvt

So, we will have this result :

productId   2001    2003
124         125     NULL
125         454     NULL
126         75      NULL
127         NULL    NULL
128         NULL    NULL
129         NULL    NULL
130         NULL    NULL
131         NULL    NULL
132         NULL    NULL
133         NULL    NULL
134         NULL    NULL
135         NULL    NULL
136         NULL    452
137         NULL    1024
138         NULL    575

Ok, that’s nice. But if we consider that we don’t know the names of the columns, we have to make our PIVOT dynamic. Look at the following code :

We are first going to build a string that concatenes all years

DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
                        '],[' + ltrim(str(_year))
                        FROM    Products
                        ORDER BY '],[' + ltrim(str(YEAR(_year)))
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'

So this string will contain all years needed for our PIVOT query:

[2001],[2002],[2003],[2004],[2005]

After that, all we have to do is to dynamically create our PIVOT query. Here is the complete query :

DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT  @years = STUFF(( SELECT DISTINCT
                        '],[' + ltrim(str(_year))
                        FROM    Products
                        ORDER BY '],[' + ltrim(str(YEAR(_year)))
                        FOR XML PATH('')
                        ), 1, 2, '') + ']'
 
SET @query =
'SELECT * FROM
(
    SELECT productId,_year,amount
    FROM Products
)t
PIVOT (SUM(amount) FOR _year
IN ('+@years+')) AS pvt'
 
EXECUTE (@query)

And here is the displayed result :

productId   2001    2002    2003    2004    2005
124         125     NULL    NULL    NULL    NULL
125         454     NULL    NULL    NULL    NULL
126         75      NULL    NULL    NULL    NULL
127         NULL    256     NULL    NULL    NULL
128         NULL    NULL    NULL    457     NULL
129         NULL    NULL    NULL    585     NULL
130         NULL    142     NULL    NULL    NULL
131         NULL    785     NULL    NULL    NULL
132         NULL    NULL    NULL    NULL    452
133         NULL    NULL    NULL    NULL    864
134         NULL    NULL    NULL    NULL    762
135         NULL    NULL    NULL    425     NULL
136         NULL    NULL    452     NULL    NULL
137         NULL    NULL    1024    NULL    NULL
138         NULL    NULL    575     NULL    NULL

Enjoy ;) PS : You might have this error message when you run the query :

Incorrect syntax near ‘PIVOT’. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

So, to enable this feature, you have to set the compatibility level of your database to a higher level by using the following stored procedure :

--If you are running SQL 2005 
EXEC sp_dbcmptlevel 'myDatabaseName', 90
--If you are running SQL 2008 
EXEC sp_dbcmptlevel 'myDatabaseName', 100