Create Dynamic Pivot Table Using Store Procedure

Introduction

 
In this article I will tell you how we can create dynamic pivot table using pivot clause and store procedure. In this article we will create a store procedure which takes some parameters and generate a pivot table.
 
In the below article I mention what is pivot clause and how we can create pivot table.
 

Create Table to Perform Operation On It

 
Here we create a table called StoreTbl which has three columns --   StoreId, The id of Store; WeekNumber, the week number of collection; and Income, the total income of that week.
  1. CREATE TABLE StoreTbl    
  2. (      
  3.     [StoreId] int,      
  4.     [WeekNumber] int,      
  5.     [Income] int      
  6. );    

Insert Data in Table

 
Now we insert some data in our table. 
  1. INSERT INTO StoreTbl    
  2. (      
  3.     [StoreId],      
  4.     [WeekNumber], [Income]      
  5. )      
  6. VALUES      
  7.     (102, 1, 96),      
  8.     (101, 1, 138),      
  9.     (105, 1, 37),      
  10.     (109, 1, 59),      
  11.     (101, 2, 282),      
  12.     (102, 2, 212),      
  13.     (105, 2, 78),      
  14.     (109, 2, 97),      
  15.     (105, 3, 60),      
  16.     (102, 3, 123),      
  17.     (101, 3, 220),      
  18.     (109, 3, 87);     
In the table we have data like shown in the below image.
 
 
Store Procedure for creating dynamic pivot
  1. SET ANSI_NULLS ON  
  2. GO  
  3. SET QUOTED_IDENTIFIER ON  
  4. GO  
  5. ALTER PROCEDURE DynamicPivotTable  
  6.          @TableName nvarchar(150),  
  7.          @FirstColumnName nvarchar(50),  
  8.          @AggregateColumnName nvarchar(50),  
  9.          @PivotColumnName nvarchar(50)  
  10. AS  
  11. BEGIN     
  12.     SET NOCOUNT ON;  
  13.     Declare @ColumnName nvarchar(max)  
  14.     Declare @SQLQuery nvarchar(max)  
  15.     Declare @TblVariable Table (columnNames nvarchar(max))  
  16.     select @SQLQuery='SELECT STUFF((select distinct '',[''+  cast( '+@PivotColumnName+' as nvarchar)+'']'' from '+@TableName   
  17.                     +' for xml path ('''') ),1,1,'''')'  
  18.     insert into @TblVariable EXEC SP_EXECUTESQL @SQLQuery  
  19.     SELECT TOP 1 @ColumnName=columnNames FROM @TblVariable        
  20.     set @SQLQuery= 'select '+@FirstColumnName+','+@ColumnName+' from (  
  21.                 select * from '+@TableName+' )  
  22.                 ST  
  23.                 PIVOT (  
  24.                         SUM('+@AggregateColumnName+'for '+@PivotColumnName+' in ('+@ColumnName+')  
  25.                 )as StorePivot'  
  26.     EXEC SP_EXECUTESQL @SQLQuery   
  27.   
  28. END  
  29. GO
Run this store procrdure with following parameters  
 
  1. EXEC DynamicPivotTable 'StoreTbl','StoreId','Income','WeekNumber'  
 
 
Output
 

Explanation 

  • First of all here I will create a store procedure called DynamicPivotTable.
  • This store procedure takes four parameters

    1. TableName
      This is your table name on which you want to perform pivot operation

    2. FirstColumnName
      This is the column name of your table which shows as first column. Mostly the column which has unique data is used as first column in pivot table.

    3. AggregateColumnName
      This is also a column of your table on which we perform aggregate operation. As we know that in the creation of pivot table we must use aggregate function on any column.

    4. PivotColumnName
      This is column of table which row value we want to show as column in our pivot table.

  • Now we declare three variables as mentioned below

    1. ColumnName
      In this string type variable we store out pivot column unique values as column name.

    2. SQLQuery
      We set our execution sql query in this variable and then execute it.

    3. TblVariable
      This is table type variable which has only one column, columnNames of nvarchar type. We first store our dynamic column name in this table variable and then set in ColumnName variable.

  • In the next step we will create a query as a string and set in SQLQuery variable. In this query we select unique value of pivot column with comma separator and then remove first comma using STUFF.
  • Then we insert in our Table variable by selecting data from our query. Here you may be confused as to why we first insert in table variable and then we get column name. If you look in query we get table name from our parameter variable so at the time of creating store procedure SQL Server Management Studio didn’t find the table name because at that time our variable is empty, so it gives a syntax error. So first we generate query as we want then we get result of that dynamic query and store in our Table Variable.
  • Then we get that value of table variable and set in ColumnName variable which we declare above.
  • Now we have all required fields which we need for creating pivot table so now we create a dynamic query for creating pivot table. Here also we create dynamic query and then execute it because we use variables in this query. You can better understand using my last article also check that out.
  • Lastly,  execute SQLQuery variable and we will get our desired output.
If you like this article and find it helpful please share with your friends and family. Thank you.