Examples Using PIVOT and UNPIVOT

How can you PIVOT or UNPIVOT data in SQL Server? What do these terms mean in SQL Server?

Let’s take a look a few examples using PIVOT and UNPIVOT.

Setting the Stage

Let’s say we have a table of movies released by DC and Marvel. We’ll focus on movies released in 2021 through 2023 and use this data as we test:

Let’s PIVOT

If we look at our Movies table, it serves as a good list, but what if we need to break it down by year? Specifically, what if we want to be able to glance and see who had the most movies each year or how consistent each studio has been?

Sure, we could use something like GROUP BY:

SELECT Release_Year, Studio, COUNT(*) AS 'Movie_Count'
FROM Movies
GROUP BY Studio, Release_Year
ORDER BY Release_Year;
GO

That works, but I think we can make it more simple. Let’s use PIVOT instead to give us the option of reading left to right to see what the counts look like over the last few years:

SELECT *
FROM 
    (SELECT Title, Release_Year, Studio
		FROM Movies
	) AS Movie_Src
PIVOT
(
	COUNT(Title)
FOR Release_Year IN ([2021], [2022], [2023])
	) AS PIVOT_Table;
GO

We have fewer fields to read. To me, that’s much easier to interpret.

UNPIVOT

UNPIVOT is the opposite of PIVOT. Instead of switching rows to columns, we can switch columns to rows. What if our original table was setup like our pivoted table:

Let’s use UNPIVOT to get back to something kind of like what we had at the beginning of this post:

SELECT Studio, Movie_Year, Movie_Count
FROM 
	(SELECT Studio, [2021], [2022], [2023]
		FROM Movies_PIVOT
	) Movie_Src
UNPIVOT
(
	Movie_Count
FOR Movie_Year IN ([2021], [2022], [2023])
	) AS UNPIVOT_Table;
GO

There We Go

I haven’t used the PIVOT and UNPIVOT operators often but the scenarios above show how they can be useful. If it slips your mind on how to use either PIVOT or UNPIVIT, maybe the examples above can help jog your memory.

Thanks for reading!