How do you use conditional aggregation to pivot row data into columns without a PIVOT keyword?

How to think about it

The interviewer wants to see you turn row-level data into a columnar report without a vendor-specific PIVOT keyword. Wrapping CASE WHEN inside an aggregate works in every SQL dialect and shows up constantly in analytics work.

The whole trick rests on one fact: CASE WHEN cond THEN x returns x when the condition holds and NULL otherwise (with no ELSE). Since COUNT and SUM both skip NULLs, only the matching rows feed each bucket — so each CASE becomes its own column from a single GROUP BY pass.

A worked example

One pass over the orders, producing per-customer status counts and per-channel revenue side by side:

SELECT customer_id,
       COUNT(CASE WHEN status = 'completed' THEN 1 END)         AS completed,
       COUNT(CASE WHEN status = 'cancelled' THEN 1 END)         AS cancelled,
       COUNT(CASE WHEN status = 'pending'   THEN 1 END)         AS pending,
       SUM(CASE WHEN channel = 'web'    THEN revenue ELSE 0 END) AS web_rev,
       SUM(CASE WHEN channel = 'mobile' THEN revenue ELSE 0 END) AS mobile_rev
FROM orders
GROUP BY customer_id
ORDER BY customer_id;

customer_id	completed	cancelled	pending	web_rev	mobile_rev
10	2	1	0	320	80
20	1	0	1	50	300
30	0	1	0	90	0

Customer 10’s two completed and one cancelled order land in separate columns, and the channel split appears too: $320 of web revenue and $80 of mobile, all from one scan. Notice the two styles — COUNT(CASE WHEN ... THEN 1 END) leans on NULL-skipping (no ELSE needed), while SUM(CASE WHEN ... THEN revenue ELSE 0 END) uses ELSE 0 so non-matching rows add zero rather than NULL.

A cleaner form where it’s available

PostgreSQL and DuckDB offer FILTER, which says the same thing more directly:

COUNT(*) FILTER (WHERE status = 'completed') AS completed

Reach for it when you can — though MySQL and BigQuery don’t support it, so the CASE form stays the portable default.