How would you pivot rows into columns in SQL?
Quick Answer
The portable, engine-agnostic technique is conditional aggregation: `GROUP BY` the row-identifying column, and use `CASE WHEN` inside an aggregate function (like `SUM` or `MAX`) once per desired output column. Some engines also offer dedicated syntax — SQL Server's `PIVOT` operator, or `crosstab()` in PostgreSQL's `tablefunc` extension — but conditional aggregation works everywhere and doesn't require knowing the pivoted values in advance to write the query structure.
Detailed Answer
The starting shape: long/narrow data
-- sales: region, quarter, amount
-- ('East', 'Q1', 100), ('East', 'Q2', 150), ('West', 'Q1', 80), ('West', 'Q2', 120)
Goal: turn this into one row per region, with a column per quarter.
Approach: conditional aggregation (portable across all major engines)
SELECT
region,
SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,
SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,
SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4
FROM sales
GROUP BY region;
| region | q1 | q2 | q3 | q4 |
|---|---|---|---|---|
| East | 100 | 150 | 0 | 0 |
| West | 80 | 120 | 0 | 0 |
This works in every SQL engine, doesn't require any extension, and is the pattern most interviewers expect as the "default" answer, since it demonstrates understanding of conditional aggregation rather than reliance on engine-specific syntax.
Engine-specific dedicated syntax
SQL Server's PIVOT:
SELECT region, [Q1], [Q2], [Q3], [Q4]
FROM sales
PIVOT (SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])) AS p;
PostgreSQL's crosstab() (requires the tablefunc extension):
CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric, q3 numeric, q4 numeric);
The key limitation: pivoted columns must generally be known ahead of time
All three approaches require you to know the distinct values that will become columns (Q1..Q4) at query-authoring time — SQL is a fixed-schema language, so a query can't natively produce a variable number of output columns based on data it hasn't seen yet. If the pivoted values are truly dynamic (unknown until runtime), you need to generate the SQL dynamically in application code or via dynamic SQL (EXECUTE/sp_executesql) — a real limitation worth mentioning, since it's a common follow-up question ("what if you don't know the quarters in advance?").