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 parameter and generate pivot table.

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

Learn About Pivot In SQL Server

Create Dynamic Pivot Table Using Store Procedure — Yogeshkumar Hadiya

Create Table to Perform Operation on It

Here we create a table called StoreTbl which has three column StoreId : The id of Store , WeekNumber : Week number of collection and Income : Total income of that week.

Insert Data in Table

Now we insert some data in our table.

In table we have data like shown in below image.

Create Dynamic Pivot Table Using Store Procedure — Yogeshkumar Hadiya

Store Procedure for create dynamic pivot

Run this store procrdure with following parameters

EXEC DynamicPivotTable ‘StoreTbl’,’StoreId’,’Income’,’WeekNumber’

Output

Create Dynamic Pivot Table Using Store Procedure — Yogeshkumar Hadiya

Explanation

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

1. TableName : This is your table name on which you want to perform pivot operation
2. FirstColumnName :This is column name of your table which show as first column. Mostly column which has unique data is use 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 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 variable as mention 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 next step we 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 have doubt that why we first insert in table variable and then we get column name because if you look in query we get table name from our parameter variable so at a time of creating store procedure SQL Server Management Studio didn’t find table name because at that time our variable is empty so it’s give 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 field which we need for creating pivot table so now we create a dynamic query for create 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 .
• In last execute SQLQuery variable and we got out desire output.

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

Source : YogeshHadiya.In

View Other Articles On My Website.