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.
CREATE TYPE c1bs_Piv AS TABLE(MyRow NVARCHAR(10),--Row IDMyCol NVARCHAR(10),--Value to become columnCalcCol INT--Value to be Summed)GODECLARE @Piv c1bs_PivINSERT INTO @PivSELECT '1','1',1 UNIONSELECT '1','s',1 UNIONSELECT '1','3',1 UNIONSELECT '2','2',6 UNIONSELECT '2','2',1 UNIONSELECT '2','3',1 UNIONSELECT '3','5',5 UNIONSELECT '3','2',1 UNIONSELECT '3','3',1 UNIONSELECT '3','3',3In 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 belowDECLARE @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 listFROM @Piv GROUP BY MyCol
--Columns list displaySELECT @Cols ColumnList,@Sel SelectList
--Complex Pivot SQLDECLARE @Params NVARCHAR(MAX)='@Piv c1bs_Piv READONLY'DECLARE @SQL NVARCHAR(MAX)='SELECT [MyRow],'+@Sel+'FROM @PivPIVOT (SUM(CalcCol)FOR MyColIN ('+@Cols+')) AS MyTable'
--Execute SQLEXECUTE 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 |
