How to use a PIVOT operator with dynamic values

Students have asked several times (every time) how to create a dynamic PIVOT query, to avoid having to hardcode the list of values.

I’ve always known that you cannot use a subquery to create the list of dates in the following example:

select PVT.* from (select * from sales.CustOrders) as C PIVOT(sum(qty) for ordermonth in ([01 Jul 06],[01 Aug 06],…etc)) as PVT

Here is a solution that uses the TSQL2012 database from MS Course 10774, broken down into steps, with some explanatory comments.

USE TSQL2012

GO

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

–build a list of month values each with a leading ','.

–The month values are converted to text using style 6. 106 would give you 4 digit years

SELECT ',' + QUOTENAME(CONVERT(VARCHAR(11),ordermonth,6))

from sales.CustOrders

GROUP BY ordermonth

ORDER BY MIN(ordermonth);

–convert the same list to xml data type with a root of '' (no xml tags <> are produced).

SELECT ',' + QUOTENAME(CONVERT(VARCHAR(11),ordermonth,6))

from sales.CustOrders

GROUP BY ordermonth

ORDER BY MIN(ordermonth) FOR XML PATH(''), TYPE;

–return the xml as a datatype of nvarchar(max). There are no tags so use '.' as the search string.

SELECT (SELECT ',' + QUOTENAME(CONVERT(VARCHAR(11),ordermonth,6))

from sales.CustOrders

GROUP BY ordermonth

ORDER BY MIN(ordermonth)

FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)');

–remove the leading ',' by replacing the first character with '' using stuff('text',1,1,'').

SELECT STUFF((SELECT ',' + QUOTENAME(CONVERT(VARCHAR(11),ordermonth,6))

from sales.CustOrders

GROUP BY ordermonth

ORDER BY MIN(ordermonth)

FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')

,1,1,'');

–save all that to the variable @cols

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(CONVERT(VARCHAR(11),ordermonth,106))

from sales.CustOrders

GROUP BY ordermonth

ORDER BY MIN(ordermonth)

FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')

,1,1,'');

–build a query as a text string so we can use the @cols variable

set @query = 'select PVT.* from (select * from sales.CustOrders) as C PIVOT(sum(qty) for ordermonth in (' + @cols + ')) as PVT'

–execute the text string query

exec(@query)

In a nutshell, it uses a query to get the values, uses “FOR XML PATH” to concatenate them into one long comma separated string, then uses STUFF() to remove the leading comma, sticks that into some dynamic sql, and executes it.