Pivoting With Conditional Aggregation
The portable CASE-inside-SUM pattern for turning rows into columns.
The Interview Setup
One of the most common reporting interview tasks is: turn rows into columns. You have a long table like sales(region, quarter, amount) and the interviewer wants a wide report with one column per quarter.
The portable, dialect-independent answer they want to hear is conditional aggregation: a CASE expression placed inside an aggregate such as SUM. Master this and you can pivot in any database, even ones with no PIVOT keyword.
Long vs Wide Form
Before pivoting, name the shapes. Long form stores one fact per row: each region/quarter pair is its own row. Wide form spreads a category across columns.
- Long: easy to insert, hard to read side by side.
- Wide: great for a human-facing report.
A pivot transforms long into wide. Interviewers love this because it tests whether you understand aggregation, not just syntax.
-- Long form (the input)
region | quarter | amount
-------+---------+-------
East | Q1 | 100
East | Q2 | 150
West | Q1 | 200
West | Q2 | 250All lessons in this course
- Pivoting With Conditional Aggregation
- Vendor PIVOT and Crosstab Syntax
- Unpivoting Columns Into Rows
- Dynamic Pivots With Unknown Columns