Creating Dynamic PIVOT Query in SQL Server

Need of Dynamic PIVOT Query

Dynamic PIVOT query resolves the limitation of static PIVOT approach where we need to update the columns ourselves after structure change.

Once we created PIVOT table and if we want to insert new records or modify data or its structure, it will not directly take the values and we need to modify data or its structure manually every time.  Hence, we need to create dynamic PIVOT queries to fix such problem.

Dynamic PIVOT includes the entire PIVOT script in a stored procedure and the procedure provide feasible options to adjust our requirements by just changing a parameter value.

Now let’s understand this with an example. We can consider below data set.

Now let’s create pivot query for above dataset. we can call it as static PIVOT query.

SELECT StudentName, English, Mathematics, Science, History FROM 
	(SELECT StudentName, Subjects, Marks FROM StudentsReport) PIVOTTable1
PIVOT 
(
	SUM (Marks) FOR Subjects IN (English, Mathematics, Science, History)
) AS PIVOTTable2

In above dataset we can see that PIVOT query has created PIVOT table and it converted the rows into columns. 

Now let’s try to insert the new data in the main dataset and see how the dataset looks like.

In the above result set, we can see that, new data (rows) inserted in main dataset. New subject “Computer” got added along with marks for both the students.

If we execute the same PIVOT query again after inserting the new records in dataset, new records won’t show up in PIVOT.

To show up the new records, we need to modify PIVOT query to add new column and insert records manually but the drawback is- it will be time consuming and error prone when large data needs to be added.

A better way to fix this problem is to create dynamic PIVOT query which will give flexibility to adjust our requirements by just changing a parameter value.

Now let’s create dynamic PIVOT query.

CREATE OR ALTER PROCEDURE dbo.usp_DynamicPivotTable
	@ColumnToPivot  NVARCHAR(255),
	@ListToPivot    NVARCHAR(255)
AS 
BEGIN

	DECLARE @SqlStatement NVARCHAR(MAX)
	SET @SqlStatement = N'
		SELECT StudentName, English, Mathematics, Science, History, Computer FROM 
			(SELECT StudentName, Subjects, Marks FROM StudentsReport) PIVOTTable1
		PIVOT 
		(
			SUM (Marks) FOR '+@ColumnToPivot+' IN ('+@ListToPivot+')
		) AS PIVOTTable2 
		';

	EXEC (@SqlStatement)

END

As you can see, dynamic PIVOT query has created Stored Procedure "usp_DynamicPivotTable".

Let’s understand what @ColumnToPivot and @ListToPivot parameters does in above script.

• @ColumnToPivot will be Subjects column and all the subjects will be displayed as column.
• @ListToPivot parameter accept all the list of values which we want to visualize as a column in the pivot table.

Now let’s execute the dynamic stored procedure and see the result.

EXECUTE dbo.usp_DynamicPivotTable 
N'Subjects', 
N'English, Mathematics, Science, History, Computer'

After executing procedure, we can see that all the records are visible which was inserted in the main dataset.

Conclusion

In this article, we have learned the challenges with static or manual PIVOT query. Post that we have gone through and understood how to address or resolve the constraints with Dynamic PIVOT approach.

Hope you’ve liked the article. Please share your feedback and suggestions in the comments section below.