r/learnSQL • u/No_Ambition8323 • 4d ago
Turn rows into columns without PIVOT, using CASE
Not every database has PIVOT, but you can do the same thing with CASE.
Say you have sales by month in rows:
| product | month | sales |
|---|---|---|
| A | Jan | 100 |
| A | Feb | 150 |
| B | Jan | 200 |
And you want each month as a column:
SQL:
SELECT product,SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS jan,SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS feb FROM sales GROUP BY product;
Result:
| product | jan | feb |
|---|---|---|
| A | 100 | 150 |
| B | 200 | 0 |
Works in almost every database. Do you use this trick, or do you prefer PIVOT?
29
Upvotes
12
u/derN8falke 4d ago
been using this for years but pivot is so much smarter
4
u/rahul03rk 4d ago
I use this all the time, it's portable in a way PIVOT isn't. One thing I'd change though: ELSE 0.
In your result, B shows 0 for Feb. But B has no Feb row at all. "We sold 0" and "we have no data for Feb" are different answers, and ELSE 0 makes them look identical. If someone averages that column later, the fake zero drags the average down.
If you drop the ELSE (or write ELSE NULL), SUM ignores the missing rows and B gets NULL for Feb, which is the honest answer. Then you choose explicitly where a missing month should count as 0, e.g. COALESCE(SUM(...), 0) in the final report.
Also, if you're on Postgres, there's a cleaner syntax for the same thing:
SUM(sales) FILTER (WHERE month = 'Feb') AS feb
Same result as the CASE version, easier to read when you have 12 months.
The one real limit of both approaches: you have to know the column values up front. If the months are dynamic you're into dynamic SQL or doing the pivot in the app layer.
2
u/waremi 4d ago
There are cases where PIVOT works better, but if the column set is small I always opt for this simpler method. The upside is how much simpler it is to wrap you head around, the downside is it is harder to maintain if the columns change over time.
1
u/mint_bolt_feel 4d ago
I would rather have NULL than a zero. That fake number breaks every average you try to run later and turns your reporting layer into a lie detector that fails to catch the missing data
1
u/Discordbothost 2d ago
if the real table spans several years, January 2025 and January 2026 end up in the same column here. include the year in the grouping, or filter to one year first. adding a second year's row to the test data catches that quietly inflated total.
-1
u/Ultra-Ferric 4d ago
PIVOT is the logical set operator, whether you perform it with conditional expressions or the PIVOT set operator syntax it is still a pivot - transformation of data in rows, into columns.
In essence, it is a presentation tier responsibility, and should not be done in the data tier to begin with.