Quick Tip 003 – Dynamic Pivot SQL

Download Source Files: Here

One of the frustrating things about using the SQL Server PIVOT function is that you need to explicitly name the field values that you want to be pivoted across the horizontal access of your result set.  The problem with this is that this list of values could change over time as new data is added to the database.  So what's the solution?  In this post I’ll demonstrate how you can create a stored proc that will take a SQL query, derive the unique values in a column and dynamically create the SQL necessary to generate PIVOT table based on these values.

Below you’ll see the code need to create a stored procedure that takes a normal select query and automatically determines the column headings for the PIVOT statement.

Let me take you through the code:

@SQL = This is a variable that will hold a normal select statement.

@Columns = This denotes the list of columns that you want to display along the vertical axis.

@PivotCol = This is the name of the column that you want to pivot across the horizontal axis.

@Summaries = This is the aggregate function and field that you want to summaries in the pivot table e.g. SUM(SalesAmount)

@Debug = 0 = Run the script, 1 = Debug the code, in this instance the SQL that is used to create the pivoted result set is printed to the results pane in SSMS.

@TableName = This is the name of a table that you want the result set inserted into.

The first thing we have to determine is the distinct field values that exists within the column that you have chosen to be the @PivotCol.  In order to do this we create a dynamic query that is placed inside a vairable @IDListSQL.  The query uses the @SQL query and the XML Path function to return a concatenated list of values, we place these in square brackets just in case some of the values have invalid characters for column names i.e. they start with a number.

In order to make the code more readable I’ve used the replace function to replace place holders in the query with the values from the actual variable.  I find this help make the code more readable and maintainable going forward, there is no need for lots of string concatenation.

I then use the sp_executesql system stored proc to execute the dynamic sql and return the unique list of column values into the @IDList varaible, this list will eventually make up our horizontal axis. 

Once we have a list of fields we then construct the final select statement.  Again I uses the replace function to switch place holders in the dynamic sql statement with the values in the actual variables.

Once this is done we can execute the SQL and generate a pivoted data set.

IF OBJECT_ID ('[sp_Pivot]',N'P') IS NOT NULL

DROP PROCEDURE sp_Pivot;

GO



CREATE PROCEDURE [dbo].[sp_Pivot]  
(          
   @SQL VARCHAR(MAX),    
   @Columns VARCHAR(MAX),  
   @PivotCol VARCHAR(MAX),  
   @Summaries VARCHAR(MAX),  
   @Debug bit = 0,  
   @TableName VARCHAR(MAX)  
)  
AS  
BEGIN

   SET NOCOUNT ON  


   DECLARE @IDList VARCHAR(MAX)  
   DECLARE @IDListSQL NVARCHAR(MAX)  
   DECLARE @PivotSQL VARCHAR(MAX)  
   DECLARE @ParmDefinition NVARCHAR(MAX)  
    
   SET @ParmDefinition = N'@IDList VARCHAR(MAX) OUTPUT'  


   SET @IDListSQL = 'SELECT @IDList = CAST ((	SELECT '',['' + @PivotCol + '']'' AS [text()]

	FROM (SELECT DISTINCT CONVERT(VARCHAR, @PivotCol ) AS @PivotCol  
	FROM ( @SQL ) AS SOURCEA ) AS SOURCEB ORDER BY @PivotCol FOR XML PATH('''')) AS VARCHAR(MAX))';

  
   SET @IDListSQL = REPLACE(@IDListSQL,'@PivotCol',@PivotCol)

   SET @IDListSQL = REPLACE(@IDListSQL,'@SQL',@SQL)



   EXEC sp_executesql @IDListSQL,@ParmDefinition,@IDList OUTPUT;  


   SET @PivotSQL = 'SELECT @Columns @IDList INTO @TableName FROM ( @SQL ) AS PVT  
	PIVOT ( @Summaries FOR @PivotCol IN (@AnotherIDList)) AS Results';



   SET @PivotSQL = REPLACE(@PivotSQL,'@TableName',@TableName)

   SET @PivotSQL = REPLACE(@PivotSQL,'@Columns',@Columns)

   SET @PivotSQL = REPLACE(@PivotSQL,'@IDList',@IDList)

   SET @PivotSQL = REPLACE(@PivotSQL,'@AnotherIDList',SUBSTRING(@IDList,2,LEN(@IDList)))

   SET @PivotSQL = REPLACE(@PivotSQL,'@SQL',@SQL)

   SET @PivotSQL = REPLACE(@PivotSQL,'@Summaries',@Summaries)

   SET @PivotSQL = REPLACE(@PivotSQL,'@PivotCol',@PivotCol)

    
   IF @Debug = 0

       EXEC (@PivotSQL)

   ELSE

       PRINT @PivotSQL  
END;

GO

Here’s an example of the proc in action.

IF OBJECT_ID ('dbo.Test',N'U') IS NOT NULL

DROP TABLE dbo.Test;

GO



EXEC sp_Pivot

   @Columns = 'CustomerAlternateKey',

   @PivotCol = 'EnglishProductCategoryName',

   @Summaries = 'SUM(SalesAmount)',

   @Debug = 0 ,

   @TableName = 'Test',

   @SQL =   'SELECT

		DimCustomer.CustomerAlternateKey,

		DimProductCategory.EnglishProductCategoryName,

		FactInternetSales.SalesAmount

	FROM	

		FactInternetSales

	INNER JOIN DimCustomer

		ON DimCustomer.CustomerKey = FactInternetSales.CustomerKey

	INNER JOIN DimProduct

		ON DimProduct.ProductKey = FactInternetSales.productkey

	INNER JOIN DimProductSubcategory

		ON DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey

	INNER JOIN dbo.DimProductCategory

		ON DimProductCategory.ProductCategoryKey = DimProductSubcategory.ProductCategoryKey';

GO

And here are the results.  You can see the values of the EnglishProductCategoryName have been pivoted across the top of the dataset.  If new Categories are added the proc will automatically cater for these.