How to Create Dynamic PIVOT Query in SQL Server

Introduction

In this article, I am going to explain how to create a dynamic pivot query in an SQL server. I'll also explain how to create a stored procedure for a dynamic pivot in an SQL server. 

This is one of the common requirements for all of us who work with data-driven applications. In my previous article PIVOT and UNPIVOT in SQL Server with Example, I explained how to convert row-level data to the column level using the PIVOT operator as well as how to convert column-level data to the row-level using the UNPIVOT operator with a simple example, but the query of PIVOT and UNPIVOT was static, So we have to pass the list of PIVOT column name manually. So, In this article, I will explain how you can create a PIVOT table dynamically using a stored procedure in an SQL server.

In my previous articles, I explained PIVOT and UNPIVOT in SQL Server with Example, How to Split Comma Separated String in SQL Server and Export JSON Data to Excel/CSV File using AngularJs With Bootstrap and Export Dataset/Datatable to CSV File Using C# and VB.NET and Bootstrap JQuery Multiselect Dropdown List With Checkbox in ASP.NET Using C# And VB.NET and How to Read CSV File In ASP.NET Webforms With Example and How to Create Missing Index From Actual Execution Plan in SQL Server and in this article I am going to explain dynamic pivot in SQL server. I also recommended the Yogesh Hadiya's blog for other informative articles of SQL server. 

Requirement 

1) What is PIVOT in SQL Server?

2) Explain How to Create Dynamic PIVOT Query in SQL Server with example.

Implementation

So, lets we start with the definition and use of PIVOT in SQL server, and understand the concept of PIVOT with a simple example.  

What is PIVOT in SQL server?

Pivot is a relational operation available in SQL server, which allows users to convert row-level data to the column level. In other words, we can say that PIVOT rotated the tables based on their unique values, mostly PIVOT used with aggregation functions such as MIN, MAX, SUM, COUNT and etc.

Now, let's take one example of a product sales report, where we will calculate year-wise total sales of the product. I took the same example in my previous article about PIVOT and UNPIVOT.

For the demonstration, we will create a table and insert some dummy records in the table for the demonstration purpose.

Create Table

CREATE TABLE [dbo].[TableSales] (
    [ProductName] VARCHAR (50) NULL,
    [TotalSales]  FLOAT (53)   NULL,
    [YearName]    INT          NULL
);

Insert Records into Table

INSERT INTO TableSales (ProductName,TotalSales,YearName) VALUES
('Shampoo',200010,2016),('TV',300500,2016),('AC',100054,2016),('Fridge',3097540,2016),('Washing Machine',289635,2016),('Mobile',29600,2016), 
('Shampoo',20440,2017),('TV',963400,2017),('AC',58963,2017),('Fridge',475693,2017),('Washing Machine',556936,2017),('Mobile',90634,2017),
('Shampoo',95415,2018),('TV',478952,2018),('AC',225688,2018),('Fridge',945630,2018),('Washing Machine',796500,2018),('Mobile',659803,2018),
('Shampoo',95415,2019),('TV',478952,2019),('AC',225688,2019),('Fridge',945630,2019),('Washing Machine',796500,2019),('Mobile',659803,2019)

When you execute the select statement for this table TableSales you can see the following result set.

SELECT * FROM TableSales ORDER BY ProductName

If you analyzed the above result set then there are 6 different products are there and total sales of each item are year wise different, and all the year is a product-wise duplicate because it is in the form of rows so it is somehow complex to get year wise sales based on the year so we will convert all the years into columns using PIVOT.

Now, we will create a stored procedure to create a dynamic PIVOT query. If you are a beginner and didn't have an idea about the stored procedure and want to learn the stored procedure in SQL server you can read my articles written on stored procedure.

Before creating a stored procedure I will show you the syntax of the stored procedure in SQL Server.

Syntax

CREATE PROCEDURE Your_Procedure_Name
-- list of parameters i.g: @Id INT = 0, @EmpName VARCHAR(50)=''
AS
BEGIN
-- Sql statements
END

Now, we will start to write a stored procedure to create a dynamic PIVOT query

Stored Procedure for Dynamic PIVOT

CREATE PROCEDURE [dbo].[spCodingvila_DynamicPivot]
		 @TableName NVARCHAR(150),
		 @UniqueColumn NVARCHAR(50),
		 @AggregateColumn NVARCHAR(50),
		 @PivotColumn NVARCHAR(50)
AS
BEGIN	
 
	SET NOCOUNT ON;
 
	DECLARE @CommaSeparatedColumnList NVARCHAR(max)
	DECLARE @SQLQuery NVARCHAR(max)
	DECLARE @TblVariable TABLE (ColumnList NVARCHAR(max))
 
	SELECT @SQLQuery='SELECT STUFF((SELECT DISTINCT '',[''+  CAST( '+ @PivotColumn +' as NVARCHAR)+'']'' FROM '+ @TableName 
					+' FOR XML PATH ('''') ),1,1,'''')'
 
	INSERT INTO @TblVariable 
	EXEC SP_EXECUTESQL @SQLQuery
 
	SELECT TOP 1 @CommaSeparatedColumnList = ColumnList FROM @TblVariable	
	
	SET @SQLQuery= 'SELECT '+ @UniqueColumn +','+ @CommaSeparatedColumnList +' FROM (
				SELECT * FROM '+ @TableName +' )
				ST
				PIVOT (
						SUM('+ @AggregateColumn +') for '+ @PivotColumn +' in ('+ @CommaSeparatedColumnList +')
				)AS StorePivot'
 
	EXEC SP_EXECUTESQL @SQLQuery 
 
END

Explanation

As, you can see in the above stored procedure, here we have taken 4 different input parameters @TableName that accept the name of the table, @UniqueColumn that indicates the first unique column in the PIVOT table, @AggregateColumn indicates the column in which we want to perform the aggregate operation and finally, @PivotColumn indicates the pivot column name that we want to show row value of column into a column in PIVOT table.

Next, we declared 3 another variable @CommaSeparatedColumnList for the get the comma-separated list of columns, @SQLQuery for creating dynamic SQL statement for generating comma-separated column list, and @TblVariable for store the list of the comma-separated column into a temporary table variable.

Now, we write the dynamic query to generate a comma-separated list of columns like ([2016],[2017],[2018],[2019]) that we want to use as a PIVOT column and stored into the @SQLQuery variable as a string. Finally, execute the created dynamic SQL query and stored the result into @TblVariable.

Finally, set the @CommaSeparatedColumnList variable from @TblVariable to get a list of comma-separated PIVOT column list, and written another dynamic query for PIVOT and stored into the same variable @SQLQuery and override the previously-stored dynamic SQL statement. Now, we executed the dynamic PIVOT query stored in @SQLQuery using SP_EXECUTESQL where SP_EXECUTESQL is enabled to execute the dynamically constructed SQL statements.

NOTE: If using the PIVOT operator then you must need to use an aggregate function like MIN, MAX, COUNT, SUM and etc.

Let's use the created stored procedure and create a PIVOT table.

PIVOT

EXEC spCodingvila_DynamicPivot 'TableSales','ProductName','TotalSales','YearName'

Explanation

As you can see here we have executed the created stored procedure spCodingvila_DynamicPivot using EXEC command and passed the required parameters on it, and finally, you can see the PIVOT table as shown in the Output below.

Output

Summary

In this article, we learned how to create and execute the dynamic pivot query in an SQL server.

If you like this article then, you can share this article as well as leave your lovely comments on this article.