MCSA-EXAM70-461-SQL: Dynamic PIVOT Query

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

Dynamic PIVOT Query

Dynamic PIVOT Query

Examples of PIVOT and Dynamic PIVOT

No comments:

Post a Comment