Using SQL Server 2005/2008 Pivot on Unknown Number of Columns (Dynamic Pivot)

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.

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