Building Dynamic Pivot Tables using SQL Server. | Essential SQL скачать в хорошем качестве

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