T-SQL Tutorial - PIVOT without using PIVOT
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 an alternative approach to using the PIVOT statement in T-SQL, if you are not familiar with PIVOT click here to watch this tutorial: • T-SQL Tutorial - PIVOTing Made Easy . The PIVOT statement is extremely useful if we want to transfer rows to columns but what if we want to perform multiple aggregations on the data and how does SQL Server handle that internally. The alternative approach is to use CASE statements and apply grouping, I walk through some examples of CASE statements that return the same results as PIVOT then add more aggregations.
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
SELECT
[Location],
SUM(CASE WHEN YEAR([Date]) = 2017 THEN Amount END) AS [Sum2017],
SUM(CASE WHEN YEAR([Date]) = 2018 THEN Amount END) AS [Sum2018],
SUM(CASE WHEN YEAR([Date]) = 2019 THEN Amount END) AS [Sum2019],
SUM(CASE WHEN YEAR([Date]) = 2020 THEN Amount END) AS [Sum2020],
AVG(CASE WHEN YEAR([Date]) = 2017 THEN Amount END) AS [Avg2017],
AVG(CASE WHEN YEAR([Date]) = 2018 THEN Amount END) AS [Avg2018],
AVG(CASE WHEN YEAR([Date]) = 2019 THEN Amount END) AS [Avg2019],
AVG(CASE WHEN YEAR([Date]) = 2020 THEN Amount END) AS [Avg2020],
max(CASE WHEN YEAR([Date]) = 2017 THEN Amount END) AS [max2017],
max(CASE WHEN YEAR([Date]) = 2018 THEN Amount END) AS [max2018],
max(CASE WHEN YEAR([Date]) = 2019 THEN Amount END) AS [max2019],
max(CASE WHEN YEAR([Date]) = 2020 THEN Amount END) AS [max2020]
FROM dbo.LocationSales
GROUP BY
[Location]
