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.
