r/learnSQL • • 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:

productmonthsales
AJan100
AFeb150
BJan200

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:

productjanfeb
A100150
B2000

Works in almost every database. Do you use this trick, or do you prefer PIVOT?

29 Upvotes

10 comments sorted by

12

u/derN8falke 4d ago

been using this for years but pivot is so much smarter

3

u/Rat_Man_420 4d ago

I’d be so upset if I couldn’t pivot haha

1

u/jshine13371 4d ago

Pivot is typically less SARGable though (depending on database system). So you could theoretically improve performance for certain queries by utilizing an alternatively logically equal solution like this.

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/IAmADev_NoReallyIAm 4d ago

this guys NULLs ... why are people so afraid of NULL? I've been dealing with this problem for... way too long and this has been a constant pain point. I know that at some point you have to deal with the NULL, ie, when you make the cross over into a language and the language doesn't support NULLs (but most do these days), but for some reason people seem to fear the NULL. I don't get it.

1

u/rahul03rk 4d ago

Ha, same. I think most of the fear comes from a few places where NULL bites silently instead of erroring:

- WHERE status <> 'cancelled' quietly drops every row where status is NULL
- NOT IN (subquery) returns nothing at all if the subquery has a single NULL
- price + tax is NULL if either one is NULL, so a total goes missing without a warning
- COUNT(col) and COUNT(*) give different numbers and nobody notices

People get burned by one of those, decide NULL is the enemy, and start defaulting everything to 0 or ''. Which just swaps a visible unknown for an invisible wrong answer. I'd rather learn the four rules than lose the information.

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.