Create Dynamic Pivot Table Using Store Procedure

Create Dynamic Pivot Table Using Store Procedure — Yogeshkumar Hadiya
Create Dynamic Pivot Table Using Store Procedure — Yogeshkumar Hadiya
Create Dynamic Pivot Table Using Store Procedure — Yogeshkumar Hadiya
Create Dynamic Pivot Table Using Store Procedure — Yogeshkumar Hadiya
Create Dynamic Pivot Table Using Store Procedure — Yogeshkumar Hadiya
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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
YogeshKumar Hadiya

YogeshKumar Hadiya

56 Followers

I am a software developer and designer work on .Net, .Net core , Angular and Ionic frameworks. Developer | Blogger | C# Corner MVP