Building Dynamic Pivot Tables using SQL Server. | Essential SQL

Let’s check out how to use SQL Pivot Tables. I’ll walk you through an example and then turn it into a dynamic pivot table.

This is useful as then we don’t need to know the column values up front when constructing the pivot. Using dynamic SQL, we’ll construct the statement with a varchar value, make it more data driven, and execute it.
If you want to make your pivot queries more flexible, then watch this video to learn how you can add variables to your SQL Server scripts and stored procedures.

I recorded this video from one of my weekly student office hours. If you looking to learn SQL, then I would recommend you join our wait list, so you’ll be the first to know when we’re offering our next class!

Check out our channel at @Kris Wenzel to learn even more about SQL Server.

Here are the scripts for those interested:

-- Pivot Table Example
WITH InventoryLocationCTE
AS
(
SELECT l.Name LocationName
,COALESCE(p.Style, 'Not Used') style
,i.Quantity * p.StandardCost TotalInventory
FROM Production.ProductInventory i
INNER JOIN production.Product p on i.ProductID = p.ProductID
INNER JOIN Production.Location l on i.LocationID = l.LocationID
)
SELECT LocationName, M, W, U, [Not Used]
FROM InventoryLocationCTE
PIVOT(SUM(TotalInventory) FOR style IN (M,W,U,[Not Used]) ) as P
ORDER BY LocationName

-- Select Statement demonstrating how we move towards a dynamic pivot table
select @pivotColumn = string_agg(style,',') + ', [Not Used]'
from (select distinct style
from production.Product
where style is not NULL
) d

[stephy]

I find this video very helpful. Thanks for sharing. Do you have a chance to show us how to make the NULL value to 0 in your example? Thanks

[jingzhou]

Kris, please share more advanced stuff like this

[CHSKnight]

Thanks Kris. I really learn a lot from your videos. Much appreciated

[CHSKnight]

I still find it hard to believe that you don't get more views and likes for such great content.

Please do a video on how to create tables(the right way). If you already have a video that covers this, then please point me to it

[jingzhou]

if you are familiar basic about pivot, and looking for using variable to dynamiclly do it, jump to 35:00, you are welcome.

[thanhvan]

Thank Kris. Can you make the text larger . It really hard to see