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!
https://www.essentialsql.com/landing/...

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

Do you love our videos? If so, you're sure to like our blog! Check it out at https://www.essentialsql.com/blog/



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