Post
Ask Question

Create Dynamic Pivot Table Using Store Procedure

Introduction

In this article, I will tell you how we can create a dynamic pivot table using pivot clauses and store procedures. In this article, we will create a store procedure that takes some parameters and generates a pivot table.

In the article below, I mention what a pivot clause is and how we can create a pivot table.

Learn About Pivot In SQL Server

SQL

Create a 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.

CREATE TABLE StoreTbl    
(      
    [StoreId] int,      
    [WeekNumber] int,      
    [Income] int    
);

Insert Data in Table

Now, we insert some data in our table.

INSERT INTO StoreTbl
(
    [StoreId],
    [WeekNumber],
    [Income]
)
VALUES
    (102, 1, 96),
    (101, 1, 138),
    (105, 1, 37),
    (109, 1, 59),
    (101, 2, 282),
    (102, 2, 212),
    (105, 2, 78),
    (109, 2, 97),
    (105, 3, 60),
    (102, 3, 123),
    (101, 3, 220),
    (109, 3, 87);

In the table, we have data like shown in the below image.

Table Data

Store Procedure for creating dynamic pivot.

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  

ALTER PROCEDURE DynamicPivotTable  
    @TableName nvarchar(150),  
    @FirstColumnName nvarchar(50),  
    @AggregateColumnName nvarchar(50),  
    @PivotColumnName nvarchar(50)  
AS  
BEGIN  
    SET NOCOUNT ON;  

    DECLARE @ColumnName nvarchar(max);  
    DECLARE @SQLQuery nvarchar(max);  
    DECLARE @TblVariable TABLE (columnNames nvarchar(max));  

    SELECT @SQLQuery = 'SELECT STUFF((SELECT DISTINCT '',[''' + CAST(' + @PivotColumnName + ' AS nvarchar) + '']''' 
                        FROM ' + @TableName + ' FOR XML PATH('''')), 1, 1, '''')';  

    INSERT INTO @TblVariable 
    EXEC SP_EXECUTESQL @SQLQuery;  

    SELECT TOP 1 @ColumnName = columnNames FROM @TblVariable;  

    SET @SQLQuery = 'SELECT ' + @FirstColumnName + ', ' + @ColumnName + ' FROM (  
                         SELECT * FROM ' + @TableName + ') ST  
                     PIVOT (  
                         SUM(' + @AggregateColumnName + ') FOR ' + @PivotColumnName + ' IN (' + @ColumnName + ')  
                     ) AS StorePivot';  

    EXEC SP_EXECUTESQL @SQLQuery;  
END  
GO

Run this store procedure with the following parameters.

EXEC DynamicPivotTable 'StoreTbl', 'StoreId', 'Income', 'WeekNumber';

Output

Output

Explanation

First of all, here I will create a store procedure called DynamicPivotTable.

This store procedure takes four parameters.

  1. TableName: This is the table name on which you want to perform the pivot operation
  2. FirstColumnName: This is the column name of your table, which shows as the first column. Mostly the column that has unique data is used as the first column in the pivot table.
  3. AggregateColumnName: This is also a column of your table on which we perform aggregate operations. As we know, in the creation of a pivot table, we must use the aggregate function on any column.
  4. PivotColumnName: This is a column of the table whose row value we want to show as a column in our pivot table.

Now, we declare three variables as mentioned below.

  1. ColumnName: In this string-type variable, we store our pivot column's unique values as a column name.
  2. SQLQuery: We set our execution SQL query in this variable and then execute it.
  3. TblVariable: This is a table-type variable that has only one column, column names of nvarchar type. We first store our dynamic column name in this table variable and then set it in the ColumnName variable.

In the next step, we will create a query as a string and set it in the SQLQuery variable. In this query, we select the unique value of the pivot column with a comma separator and then remove the first comma using STUFF.

Then, we insert ii into our Table variable by selecting data from our query. Here, you may be confused as to why we first insert the table variable and then get the column name. If you look at the query, we get the table name from our parameter variable, so at the time of creating the store procedure, SQL Server Management Studio didn’t find the table name because, at that time, our variable was empty, so it gave a syntax error. So first, we generate a query as we want, and then we get a result of that dynamic query and store it in our Table Variable.

Then, we get that value of the table variable and set it in the ColumnName variable, which we declare above.

Now we have all the required fields which we need for creating a pivot table so now we create a dynamic query for creating a pivot table. Here, we also create a 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 the SQLQuery variable and we will get our desired output.

If you like this article and find it helpful, please share it with your friends and family. Thank you.


Similar Articles