Recently I came across a need for creating dynamic pivots. I found that my requirement were such that I’d need to write a PIVOT in my stored procedure inside MS SQL Server but at the same time the fields that would be pivoted will be determined at run time. After reading few blogs and forums, I found couple of database experts recommending this approach:
- Create a variable in SQL. e.g. DECLARE @sql nvarchar(MAX) , @cols nvarchar(MAX). We keep the variable length MAX to allow any length of text.
- Set the variable to empty string e.g. SET @cols = ”. This will be useful when we do the SELECT in next step and perform concatenation. Without this step, the next step would always evaluate to NULL(the default for newly declared variable in SQL) unless, we use COALESCE in step 3
- Concatenate and store all the values for pivots in the variable using a sub query. e.g. SELECT @col = @col + QUOTENAME[columnname]+’ ,’ FROM ( SELECT DISTINCT [columnname] FROM [tablename]. This will set the all the records from the sub query in the variable but in a single string seperated by comma.
- We then need to remove the trailing comma created in step 3. e.g. SET @col = LEFT(@col, LEN(@col)-1)
- Now this is the main step needs due diligence. We need to set the entire SQL into a variable and keep placeholders for pivots. e.g. SET @sql = N’SELECT * FROM ( SELECT [colum1], [column2] FROM [tablename] ) t PIVOT ( SUM[columnname] FOR [pivotcolumnname] in ($col$) ) AS PIVOT’
- Once we get our initial SQL ready, we can now replace those placeholder i.e. $col$ by our pivoting values. e.g. SET @sql = REPLACE(@sql, ‘$col$’, @col);
- Finally, we need to execute this sql via system stored procedure. e.g. EXEC sp_executeSQL @sql. If we want to check the result first before executing the sql, we can print it first and comment out the EXEC sql. e.g. PRINT @sql
This is farily simple but very powerful SQL which basically means you can create any PIVOTS on any values without having to specify them before hand.