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

[@harmeetlamba5571]

BeardedDev, thanks for creating such content. You are my go-to before every interview. And tomorrow is one

[@BeardedDevData]

Good luck with the interview

[@BijouBakson]

Pretty useful. This topic got me stalled for a moment. I think I'm beginning to crack it now. Thank you.

[@nickt423]

This is the best video I've seen on this topic. Thank you!

[@BeardedDevData]

Thanks so much

[@wolframfoxhole5586]

Great video man. Helping me out on interview prep.

[@BeardedDevData]

Thanks so much. Good luck with the interview

[@ny3nq]

Great Videos!!! Could you please do a video for Pivot Tables when Joining multiple tables.

[@johncarney9515]

Superb. Thank you

[@BeardedDevData]

Thanks so much 👍

[@raymondjurado9203]

First. This is good training.

[@BeardedDevData]

Thanks so much, I’m trying out new software, if you could let me know what you think of the zooming and sound quality that will be much appreciated.

[@raymondjurado9203]

@@BeardedDevData That was a good use of that zooming function to bring focus to the context of the elements in our syntax review. Your volume was a little low relative to other stuff, but I was able to crank my volume. Thanks again. I've used this to good effect in the past, but the syntax takes repetition to get the build order right.

[@makhan3462]

Great video. Do you know speed/performance comparisons between sql pivot and spark df pivot?

[@BeardedDevData]

That's an excellent question, I will have to do some testing on that ensuring I've got the hardware the same or as close as possible.

[@granand]

Thank you very much. Please can you point me to the video of Dynamic Pivoting and Metrics like TOP 5, Bottom 5 and creating a funnel chart etc

[@BeardedDevData]

I will upload a video on dynamic pivot shortly, are you looking for videos in a visualisation tool? I've been thinking of doing some Power BI videos for a while

[@granand]

@@BeardedDevData Yes I will be keen on Power BI but for now, I am doing a project on T-SQL and the client wants a lot of Pivot-based charts and visualizations and the next phase, they want to use this SQL as the source to Power BI and if you doing videos co-relating your SQL videos to Power BI you will be very unique, actually not that I know when I searched. I would greatly appreciate that kind of Co Relation to solve a problem in SQL and use the same SQL or source data to do in Power BI. A great source of knowledge.

[@mxderry]

Thanks for the vid...I'm trying to figure out how to handle a second amount column. Amount1 and Amount2 summed up to one row per location. Same date fields spread out. Is that a second pivot?

[@BeardedDevData]

Can you tell me the format of your data, what are the columns and rows? From the sounds of it there are two approaches, you can either perform addition in the derived table - input to PIVOT or if you want to perform multiple PIVOT operations you can use CASE statements - I talk about this in another video, ua-cam.com/video/xPMbgM8Eb2k/v-deo.html

[@mxderry]

@@BeardedDevData Yep, I figured out how to add a CASE statement to determine the 'Spread' fields. I've also figure out how to add a second pivot into the mix which adds more to that spread. Thanks!

[@harisetiaji8941]

How to make pivot for 2 table? in your video the case is 1 table

[@BeardedDevData]

You will need to join the tables in the derived table that is used as input to the pivot operation.