Got a question? Call 1800 853 276   |   

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

Our AIICT brand expands portfolio with ten new courses to help address ICT skills shortage
The Australian Institute of ICT (AIICT) has introduced a new series of industry certified bootcamp programs and nationally-recognised qualifications to meet the surging demand for skilled ICT professionals in Australia.  The bootcamps support the Morrison Government’s recently announced Digital Skills Organisation (DSO) pilot, which recognises the importance of non-accredited training to support the development of skills of the future workforce. The bootcamp programs run for six months and comprise of several vendor-specific certifications. The courses include ‘Cloud Computing Certified Professional’, ‘Certified Microsoft Full Stack Developer’, ‘Certified Artificial Intelligence Professional’, ‘Growth Marketing Professional’ and ‘Certified Project Management Professional’. The decision to introduce the bootcamps follows the VET sector’s increasing move away from nationally recognised qualifications to vendor-specific, industry-certified training. According to the National Centre for Vocational Education Research, preference for accredited training courses has declined steadily in recent years, with employers increasingly less satisfied that these courses provide their employees with the most relevant and important skills for their business. This has led many organisations to preference non-accredited training provided by private technology vendors such as Microsoft and AWS.
Read more...