In this article I 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.
Pivot is one the new relational operator introduced in SQL Server 2005. It provides easy mechanisms in SQL Server to transform rows into columns.
Let’s move towards our example, sql script for table and sample records are given at the end of this article.
First Create a Table Product with sample records as depicted in the below image:
Now create a table Customer by using following script:
Last and final table Order script is given below:
Let us first understand the STATIC PIVOT query and then see how we can modify this Static PIVOT query to Dynamic.
By using the above query we want to show that how many orders has been received for the product from the specific City. The out should be something like below:
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. The query is demonstrated below:
Thanks for reading this article. Please suggest me any improvement for this article if you find and do not forgot to enter your valuable comments.
SQL Script for this article example: