PIVOT is sugar for a GROUP BY plus per-bucket aggregates. CASE WHEN does the same thing with explicit branches.
When PIVOT wins
- You're on SQL Server, Snowflake, or Oracle and the bucket list is small and known.
- Readability matters more than portability.
When CASE WHEN wins
- You're on Postgres or MySQL, where PIVOT is not native.
- You need conditional aggregates beyond a single column (e.g., SUM CASE for one column, AVG CASE for another).
- Your bucket list is dynamic — you'd be code-generating the PIVOT clause anyway.
Lineage view
Both approaches collapse to the same lineage shape: one source column feeds many output columns, partitioned by the bucket key. The pivot example shows this clearly.