SQL Server using PIVOT with dynamic SQL example

SQL Server using a complex PIVOT function in a real world type scenario, where the number of columns may not always be the same.

In the last article, I wrote about using PIVOT in a standard scenario where you know all of the columns that you want, using the same data we can now build on that to produce column names dependent on the actual data set.

I will skip the first part of that article but provide the SQL to get us up and running here. First, lets create a data set.

More: SQL Server using PIVOT example

CREATE TYPE c1bs_Piv AS TABLE(MyRow NVARCHAR(10),--Row ID
MyCol NVARCHAR(10),--Value to become column
CalcCol INT--Value to be Summed
)
GO
DECLARE @Piv c1bs_Piv
INSERT INTO @Piv
SELECT '1','1',1 UNION
SELECT '1','s',1 UNION
SELECT '1','3',1 UNION
SELECT '2','2',6 UNION
SELECT '2','2',1 UNION
SELECT '2','3',1 UNION
SELECT '3','5',5 UNION
SELECT '3','2',1 UNION
SELECT '3','3',1 UNION
SELECT '3','3',3

In the other article, we hard coded the column names (1,2,3), however, this may not always be viable, sometimes the record sets may need to grow when for example sending a data set to a GUI, there is a way around it, lets go through it step by step;

Create a Table Type to represent your data as above.
Declare a table and insert your data into it (@Piv)
Declare two variables, one to hold the column names to Pivot, and one for the SELECT list, in this example, using a ISNULL and the column names gets rid of any NULL values. We have also used COALESCE to concatenate the names into a string.
Declare another variable, with the @Piv table, these are parameters that get passed on later.
Declare our SQL, this is where we inject the column names and select list into a modified version of the simple PIVOT.
EXECUTE the SQL statement above, and pass in the parameters and @PIV table.

--Complex Pivot - unknown column names
--Get Column Names for below
DECLARE @Cols NVARCHAR(MAX),@Sel NVARCHAR(MAX)
SELECT @Cols=COALESCE(@Cols+',','')+'['+MyCol+']',--Concatenate the Columns
@Sel =COALESCE(@Sel +',','')+'ISNULL(['+MyCol+'], 0) AS ['+MyCol+']' --Concatenate the Columns into a select list
FROM @Piv GROUP BY MyCol

--Columns list display
SELECT @Cols ColumnList,@Sel SelectList

--Complex Pivot SQL
DECLARE @Params NVARCHAR(MAX)='@Piv c1bs_Piv READONLY'
DECLARE @SQL NVARCHAR(MAX)=
'SELECT [MyRow],'+@Sel+'
FROM @Piv
PIVOT (SUM(CalcCol)
FOR MyCol
IN ('+@Cols+')
) AS MyTable'

--Execute SQL
EXECUTE sp_executesql @SQL,--SQL String from above
@Params,--Parameter list
@Piv--Temp Table needs to be passed in, can only be read only?

Our dataset has now grown to include two additional columns from the dataset (5,s)

MyRow	1	2	3	5	s
1	1	0	1	0	1
2	0	7	1	0	0
3	0	1	4	5	0