Dynamic PIVOT in Sql Server

March 18, 2014Sql Server, Sql Server 2005Dynamic PIVOT, Dynamic PIVOT Column Names, Dynamic PIVOT Example, Dynamic Pivot in Sql Server, PIVOT, Sql, Sql Server, Static PIVOT QueryBasavaraj Biradar

In the Previous Post PIVOT and UNPIVOT in Sql Server explained how PIVOT relational operator can be used to transform columns distinct values as Columns in the result set by mentioning all the distinct column values in the PIVOT operators PIVOT columns IN clause. This type of PIVOT query is called Static PIVOT query, because if the PIVOT column in the source table get’s extra unique values after the initial query then that will not reflect in the PIVOT query result unless it is mentioned in the PIVOT Columns IN clause. Static PIVOT queries are fine as long as we know that the PIVOT column values never change, for instance if PIVOT column values are MONTH or Day of the Week or hour of the day etc.

In this Article will present how we can write a Dynamic PIVOT query with an example, where we don’t need to mention the PIVOT columns each unique values and no need to worry if PIVOT column gets extra unique values after the initial query.

ALSO READ: PIVOT and UNPIVOT in Sql Server

First Create a Temporary Table #CourseSales with sample records as depicted in the below image by using the following script:

--Create Temporary Table #CourseSales

CREATE TABLE #CourseSales

(Course VARCHAR(50),Year INT,Earning MONEY)

GO

--Populate Sample records

INSERT INTO #CourseSales VALUES('.NET',2012,10000)

INSERT INTO #CourseSales VALUES('Java',2012,20000)

INSERT INTO #CourseSales VALUES('.NET',2012,5000)

INSERT INTO #CourseSales VALUES('.NET',2013,48000)

INSERT INTO #CourseSales VALUES('Java',2013,30000)

GO

PIVOT #CourseSales Table data on the Course column Values

Let us first understand the Static PIVOT query and then see how we can modify this Static PIVOT query to Dynamic.

Static PIVOT query

Below Static PIVOT script pivots the #CourseSales Table data so that the Course columns distinct values are transformed as Columns in the result set as depicted in the above image.

SELECT *

FROM #CourseSales

PIVOT(SUM(Earning) 

      FOR Course IN ([.NET], Java)) AS PVTTable

RESULT:

Let us insert one more row in the #CourseSales table for the new course SQL Server with below insert statement.

INSERT INTO #CourseSales VALUES('Sql Server',2013,15000)

Now rerun the above PIVOT query.
RESULT:

From the above result it is clear that the newly added course Sql Server sales data is not reflected in the result.

Dynamic PIVOT Query

To make the above Static PIVOT query to dynamic, basically we have to remove the hardcoded PIVOT column names specified in the PIVOT operators PIVOT columns IN clause. Below query demonstrates this.

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)

DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column 

SELECT @ColumnName= ISNULL(@ColumnName + ',','') 

       + QUOTENAME(Course)

FROM (SELECT DISTINCT Course FROM #CourseSales) AS Courses

--Prepare the PIVOT query using the dynamic 

SET @DynamicPivotQuery = 

  N'SELECT Year, ' + @ColumnName + '

    FROM #CourseSales

    PIVOT(SUM(Earning) 

          FOR Course IN (' + @ColumnName + ')) AS PVTTable'

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery

[ALSO READ] How to replace NULL value by 0 in the Dynamic Pivot result – Sql Server
RESULT:

From the above result it is clear that this query is a True Dynamic PIVOT query as it reflected all the courses in the #CourseSales table without needing to write hardcoded course names in the PIVOT query.

Examples of PIVOT and Dynamic PIVOT

Below are the some of the examples of retrieving data in Sql Server using PIVOT and Dynamic PIVOT:

MCSA-EXAM70-461-SQL

Friday, July 22, 2016