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 :

1 2	SELECT productId,_year,amount FROM Products

We have this result :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 :

1 2 3 4 5 6 7

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 :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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

1 2 3 4 5 6 7

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:

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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 :

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 :

1 2 3 4

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