T-SQL Tutorial - PIVOTing Made Easy скачать в хорошем качестве

T-SQL Tutorial - PIVOTing Made Easy

Another video brought to you by BeardedDev, bringing you tutorials on Data Engineering, Business Intelligence, T-SQL Programming and Data Analysis. If you like the videos you can support me on Patreon,   / beardeddev   In this video I talk about the PIVOT statement in T-SQL, I talk about the two different types of PIVOT; Many-To-One and One-To-One and also the operations; Grouping, Spreading and Aggregating that make up PIVOT. I also show how you can create your data to practice using PIVOT in SQL Server and then walk through some PIVOT examples. You can use the code examples below to follow along. Please feel free to post any comments Code Examples: IF OBJECT_ID(N'dbo.LocationSales', N'U') IS NOT NULL DROP TABLE dbo.LocationSales; GO CREATE TABLE dbo.LocationSales ( [Location] [varchar](20) NOT NULL, -- grouping [Date] [date] NOT NULL, -- spreading Amount [decimal](6, 2) NOT NULL -- aggregating ) INSERT INTO dbo.LocationSales ([Location], [Date], Amount) VALUES ('Birmingham', '20170101', 1564.82), ('Birmingham', '20170201', 3984.64), ('Birmingham', '20180101', 6821.46), ('Birmingham', '20180201', 7018.56), ('Birmingham', '20180301', 6736.89), ('Birmingham', '20190101', 7832.93), ('Birmingham', '20190201', 6821.46), ('Birmingham', '20190301', 6821.46), ('Birmingham', '20200101', 8901.76), ('Birmingham', '20200201', 6821.46), ('Birmingham', '20200301', 6821.46), ('London', '20180101', 3755.81), ('London', '20180201', 4512.01), ('London', '20190101', 5087.54), ('London', '20190201', 4309.53), ('London', '20190301', 5168.38), ('London', '20200101', 8206.48), ('London', '20200201', 9468.54), ('London', '20200301', 5087.54), ('Manchester', '20190101', 2472.72), ('Manchester', '20190201', 5937.28), ('Manchester', '20200101', 5369.37), ('Manchester', '20200201', 5862.94), ('Manchester', '20200301', 4674.28); -- 3. Create SELECT SELECT [Location], [2017], [2018] [2019], [2020] FROM -- 1. Create a Derived Table ( SELECT [Location], YEAR([Date]) AS [Year], Amount FROM LocationSales ) AS D -- 2. Create PIVOT PIVOT ( SUM(Amount) FOR [Year] IN ([2017], [2018], [2019], [2020]) ) AS P -- PIVOT -- ONE to ONE Example -- 3. Create SELECT SELECT [Location], [2017], [2018], [2019], [2020] FROM -- 1. Create a Derived Table ( SELECT [Location], YEAR([Date]) AS [Year], SUM(Amount) AS Amount FROM dbo.LocationSales GROUP BY [Location], YEAR([Date]) ) AS D -- 2. Create PIVOT PIVOT ( MAX(Amount) FOR [Year] IN ([2017], [2018], [2019], [2020]) ) AS P