My attempt to make a dynamic PIVOT statement. The table used is the same as the example in my 70-761 book.
First, the @list_of_columns parameter is filled to create a list of columns, using COALESCE. Next a common table expression is used. In this example. it is not useful, but as more complicated PIVOT statements will usually work with tables with more than the required 3 columns, I left this cte in there.
Add a second @list_of_columns parameter to replace the NULL values in the result set.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
DECLARE @list_of_columns VARCHAR(max) , @my_dynamic_pivot_statement VARCHAR(max); WITH cte AS ( SELECT DISTINCT Customer FROM Sales ) SELECT @list_of_columns = COALESCE(@list_of_columns + '], [', '[') + CONVERT(VARCHAR(100),Customer) FROM cte ; IF RIGHT(@list_of_columns, 1) <> ']' SET @list_of_columns = @list_of_columns + ']' SELECT @list_of_columns AS results; SET @my_dynamic_pivot_statement = ' WITH cte as (--left the cte for more complicated examples select * from sales) SELECT [Product], ' + @list_of_columns+' FROM (select * from cte) as sourcetable PIVOT ( SUM(Quantity) FOR Customer IN (' +@list_of_columns+ ') ) AS pivottable ORDER BY product;' SELECT @my_dynamic_pivot_statement; EXEC (@my_dynamic_pivot_statement); |