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.



Feature Articles


eBook
Drive Innovation with IT Service Management Training
11 December 2023
eBook
Get your teams up-to-speed with ITIL® 4
22 May 2024
Blog
How did your incident management plan stand up to the CrowdStrike outage?
By Gary Duffield | 23 July 2024
eBook
Elevate your business and career to new heights
22 May 2024
Blog
Understanding PRINCE2 Version 6 vs 7: Themes, risks & issue management
By Fred Carenese | 21 May 2024
Blog
The Growing Importance of Management Skills and the AMA CPM Certification in 2024
By Gary Duffield | 29 July 2024