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
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)
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
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:
- How to get Hourly data in Sql Server
- How to get Daily data in Sql Server
- How to get Monthly Data in Sql Server
- How to get Quarterly Data in Sql Server
- How to get Yearly data in Sql Server
PIVOT and UNPIVOT in Sql Server