This may seem crazy to some of you, but so far in my career I have been able to successfully avoid learning how to use the PIVOT feature of queries in SQL. Every example I ever came upon in code someone else wrote, I was able to get the gist of what was happening in the code without truly understanding what the query was doing. Until this week when I decided enough was enough and that writing a PIVOT query was going to be the easiest way to get the data I needed to check my work on project I am developing. This post won’t go into too much detail about writing complicated PIVOT queries, because the one I wrote wasn’t that complicated, all things considered. I mostly want to celebrate and share that I finally learned how to do something that I thought was going to be more difficult than it was.
What’s in this post
My use case
The reason why I decided I needed to finally learn how to write a PIVOT query is that I am working on duplicating and automating a manual system a business user does every quarter to project sales data using a very complicated Excel spreadsheet. As I’m working on turning this spreadsheet into an automated system with SQL and Python (since we have the same data in a database just waiting to be used), I am constantly checking my queries and results against the source of truth that is the Excel spreadsheet.
This morning, I was tired of essentially having to PIVOT my data manually or in my head to check my work, so set about learning the PIVOT query structure.
The data in the spreadsheet looked something like this:
| State | County | 2020 | 2021 | 2022 | 2023 | 2024 |
|---|---|---|---|---|---|---|
| ID | Ada | 1000 | 1283 | 1476 | 1923 | 4657 |
| ID | Canyon | 54 | 55 | 65 | 76 | 87 |
| ID | Boise | 54 | 55 | 65 | 76 | 87 |
| ID | Valley | 54 | 55 | 65 | 76 | 87 |
But when I was querying the data from the database, the results of my SQL queries were looking more like this:
| State | County | Year | Sales |
|---|---|---|---|
| ID | Ada | 2020 | 1000 |
| ID | Ada | 2021 | 1283 |
| ID | Ada | 2022 | 1476 |
| ID | Ada | 2023 | 1923 |
| ID | Ada | 2024 | 4657 |
If it was actually that few rows of data, it wouldn’t be a problem, but I was needing to compare the data coming from my queries against what was in a spreadsheet that had over 3000 lines (there are so many more counties in the US than I ever would have guessed…). And I wanted to do the comparison using Excel so that I didn’t have to manually compare the spreadsheet data against my own, row-by-row. So in comes the pivot query to make my data look more like the data in the spreadsheet.
Writing the PIVOT Query
Learning how to write the query was more challenging than I expected because it seems like in the top results and examples online, even in the Microsoft docs, the queries are written in a confusing and non-intuitive way so I couldn’t figure out how to manipulate them to fit my own needs. The exception to this was this wonderful YouTube video by BeardedDev, which finally helped me figure out how to write my first pivot query that was actually what I needed it to be.
His video was so much more helpful than the written tutorials and docs that I looked through because he started out with the outline of what the query should contain, showed the general query he was starting with that he wanted to convert, then walked through step by step how to switch the original query into a PIVOTed query. I highly recommend watching that video. I don’t even want to try to explain it in my own words because he did it so well.
In the end, this ended up being the query I wrote which is exactly what I needed to make the formatting of my queries match what was already in the spreadsheet from the business user.
SELECT
[State], [County], [2020], [2021], [2022], [2023], [2024]
FROM
(
select [State], [County], YEAR, Sales
from dbo.HistoricSales
) AS Src
PIVOT
(
SUM(Sales)
FOR [YEAR] IN ([2020], [2021], [2022], [2023], [2024])
) AS Pvt
order by [State], [County]Summary
Writing a PIVOT query in SQL isn’t super difficult if you are given the right examples and instructions. I highly recommend checking out BeardedDev’s video that I linked above for learning how to write a PIVOT query the easy way. That video is what finally helped me understand that type of query after working with SQL for over 6 years and not being able to understand it.
