Pivoting & reshaping
SQL · 5 interview questions
Pivoting means taking values that live in rows and making them into columns — one row per department with a column per quarter, rather than one row per department-quarter pair. Most databases have no PIVOT keyword, so the technique is conditional aggregation, and it's worth learning as a shape rather than as syntax.
The shape is always the same: SUM(CASE WHEN condition THEN value ELSE 0 END) or COUNT(CASE WHEN condition THEN 1 END), one expression per output column, wrapped in a GROUP BY over whatever should become the row.
Choosing between SUM and COUNT matters here. COUNT counts non-NULL results, so a CASE with no ELSE yields NULL for non-matching rows and they're correctly ignored — but adding ELSE 0 breaks it, because 0 is not NULL and gets counted. With SUM, ELSE 0 is harmless and often clearer. Pick one convention and be consistent.
The reverse operation, unpivoting columns back into rows, is normally a UNION ALL — one SELECT per source column. Both directions are common in reporting work and both come up in interviews, usually phrased as "reformat this table".
Pivoting & reshaping interview questions
- How do you pivot rows into columns without a PIVOT keyword?
- Conditional aggregation: one
SUM(CASE WHEN col = 'x' THEN value ELSE 0 END)per output column, grouped by whatever should become the row. Most engines including MySQL have no PIVOT, so this is the technique. - Why they ask: The 'reformat the department table' family of problems, all at once.
- Why does
COUNT(CASE WHEN x THEN 1 ELSE 0 END)count everything? - COUNT counts non-NULL values, and 0 is not NULL. Drop the ELSE so non-matching rows produce NULL and are skipped, or switch to SUM where
ELSE 0is correct. - Why they ask: A subtle bug that gives you the total row count instead of the conditional count.
- How do you turn columns back into rows?
- UNION ALL, one SELECT per source column, each emitting a label and the value. UNION ALL rather than UNION — you don't want identical values across columns silently deduplicated.
- Why they ask: The UNION vs UNION ALL choice is the part that goes wrong.
- What if the set of pivot columns isn't known in advance?
- Plain SQL can't do it — the column list has to be fixed at parse time. You either generate the SQL dynamically from a first query, or return the long format and let the application reshape it. Saying this plainly is the right answer, not a dodge.
- Why they ask: Tests whether you know SQL's limits rather than trying to fake past them.
- In a pivot, should a missing combination show as 0 or NULL?
- It's a product decision and you should ask. NULL says 'no data', 0 says 'measured, and it was zero'. In reporting they mean different things, and picking silently is how a dashboard ends up lying.
- Why they ask: One of the few SQL questions where the right answer is a clarifying question.
Practise it
Free, database-tagged LeetCode problems that drill this specific skill. In the app these tick themselves off from your solve history.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start your streakFull refund within 7 days. No questions.