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