Dynamic PIVOT query in SQL Server

PIVOT clause is used to generate cross tab outputs in SQL Server. We put unique data values from a column in the PIVOT clause to render them as multiple columns in aggregation with other columns required in the output. With the help of PIVOT clause, we can transpose the distinct values of a column into multiple columns. However, syntax of PIVOT clause requires these distinct values to be known at query design time. This kind of query can be considered as a static pivot query. We can hard code these distinct values in the PIVOT clause only if these values are fixed and static in nature.

However, when it comes to pivot uncertain values from a column, we would’t be able to cater these values in the PIVOT clause at query design time. Also, SQL Server does not provide any built in functionality which can be used in this scenario. But as we know that SQL Server is a robust enterprise database, it facilitates the power of dynamic SQL which can generate and execute T-SQL commands dynamically, this technique can be used to generate and execute dynamic PIVOT query. We can execute the dynamic PIVOT query with the help of EXECUTE or SP_ExecuteSQL command. To know the difference between EXECUTE and SP_ExecuteSQL commands, visit this click here.

We have below “ShoppingDetail” table which stores the details of the customer and their expenses made for each product category on a given time. Use below command to create this dummy table:

CREATE TABLE dbo.ShoppingDetail
(
CustomerName VARCHAR(256) NOT NULL,
ProductCategory VARCHAR(256) NOT NULL,
TotalAmount NUMERIC(18, 2) NOT NULL
)

Insert few sample rows as below:

INSERT INTO dbo.ShoppingDetail
(CustomerName, ProductCategory, TotalAmount)
SELECT 'Roy Martin' AS CustomerName, 'Clothing' ProductCategory, 2967 TotalAmount
UNION ALL
SELECT 'Roy Martin' AS CustomerName, 'Accessories' ProductCategory, 2458.00 TotalAmount
UNION ALL
SELECT 'Roy Martin' AS CustomerName, 'Bikes' ProductCategory, 52478.00 TotalAmount
UNION ALL
SELECT 'Caitlin C Watson' AS CustomerName, 'Clothing' ProductCategory, 3289 TotalAmount
UNION ALL
SELECT 'Caitlin C Watson' AS CustomerName, 'Bikes' ProductCategory, 75769.00 TotalAmount
UNION ALL
SELECT 'Taylor Torres' AS CustomerName, 'Clothing' ProductCategory, 2875 TotalAmount
UNION ALL
SELECT 'Taylor Torres' AS CustomerName, 'Accessories' ProductCategory, 6567.00 TotalAmount
UNION ALL
SELECT 'Taylor Torres' AS CustomerName, 'Bikes' ProductCategory, 46897.00 TotalAmount
UNION ALL
SELECT 'Taylor Torres' AS CustomerName, 'Food Products' ProductCategory, 15783.00 TotalAmount

Have a look on the table data:

Below is the required cross tab output:

We can see that the all unique customers are on columns and distinct product categories are in rows with total amount as values.

Before going to create this output dynamically using dynamic SQL, first have a look on static pivot query in below section.

Static PIVOT Query

To get the above output, we can simple write the below PIVOT query:

SELECT ProductCategory,
ISNULL([Roy Martin], 0) AS [Roy Martin],
ISNULL([Caitlin C Watson], 0) AS [Caitlin C Watson], ISNULL([Taylor Torres], 0) AS[Taylor Torres]
FROM dbo.ShoppingDetail
PIVOT
(
SUM(TotalAmount) FOR CustomerName IN([Roy Martin], [Caitlin C Watson], [Taylor Torres]) --Customer names hard coded here
) AS PVT

In this query, we can see that the distinct values from “CustomerName” column are hard coded in the PIVOT clause.

Dynamic PIVOT Query

Considering the case where newly added customers and product categories in the table needs to be reflected in the output immediately, we need to create a dynamic PIVOT query to fulfill this requirement as below:

DECLARE @SQLStatement NVARCHAR(MAX) = N'' --Variable to hold t-sql query
DECLARE @UniqueCustomersToPivot NVARCHAR(MAX) = N'' --Variable to hold unique customers to be used in PIVOT clause
DECLARE @PivotColumnsToSelect NVARCHAR(MAX) = N'' --Variable to hold pivot column names with alias to be used in SELECt clause
--Extarct unique customer names with pivot formattings
SELECT @UniqueCustomersToPivot = @UniqueCustomersToPivot + ', [' + COALESCE(CustomerName, '') + ']' FROM (SELECT DISTINCT CustomerName FROM dbo.ShoppingDetail)DT
SELECT @UniqueCustomersToPivot = LTRIM(STUFF(@UniqueCustomersToPivot, 1, 1, '')) --Remove first comma and space
--Generate column names to be put in SELECT list with NULL handling and aliases also
SELECT @PivotColumnsToSelect = @PivotColumnsToSelect + ', ISNULL([' + COALESCE(CustomerName, '') + '], 0) AS [' + CustomerName + ']'
FROM (SELECT DISTINCT CustomerName FROM dbo.ShoppingDetail)DT
--Generate dynamic PIVOT query here
SET @SQLStatement =
N'SELECT ProductCategory '
+ @PivotColumnsToSelect +
'
FROM dbo.ShoppingDetail
PIVOT
(
SUM(TotalAmount) FOR
CustomerName IN
(' + @UniqueCustomersToPivot + ')
) AS PVT
'
--Execute the dynamic t-sql PIVOT query below
EXEC (@SQLStatement)

We have used inline comments in above query to explain the important steps. In above query, we are using the power of dynamic SQL to create the pivot query at run time and then executing it with the help of EXECUTE command. Here, we are creating pivot query at run time rather than design time and that is it.

I hope you have enjoyed this post and will share your inputs in the comment section of this article. I request you to post, share and like this blog if it is a worth reading for you.

Thank you for the reading.

To read more about “Using PIVOT and UNPIVOT” clauses, visit this link.