SQL Pivoting

Transform row data into columns (pivot) or columns into rows (unpivot). Standard SQL has no native PIVOT — CASE WHEN inside an aggregate is the portable answer.

Manual Pivot with CASE WHEN + MAX

Input (long format): one row per (user, metric). Output (wide format): one row per user, one column per metric.

SELECT user_id,
  MAX(CASE WHEN metric = 'clicks'      THEN value END) AS clicks,
  MAX(CASE WHEN metric = 'impressions' THEN value END) AS impressions,
  MAX(CASE WHEN metric = 'conversions' THEN value END) AS conversions
FROM user_metrics
GROUP BY user_id;

Why MAX, not SUM: after GROUP BY user_id, each group logically has exactly one non-NULL value per metric column (the CASE returns NULL for every row that doesn't match) — MAX simply picks out that one real value, ignoring the NULLs. SUM would also work by coincidence when there's truly one match, but MAX communicates "pick the single value" intent more precisely, and SUM is the right choice specifically when totaling multiple matching rows (see the count/sum pattern below).

Pivot: Count / Sum Pattern

SELECT DATE_TRUNC('month', created_at) AS month,
  COUNT(CASE WHEN status = 'delivered' THEN 1 END) AS delivered,
  SUM(CASE WHEN status = 'delivered' THEN amount ELSE 0 END) AS delivered_revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

SUM with ELSE 0 when a numeric total across possibly-many matching rows is needed. COUNT without ELSE when counting non-NULL matches (NULLs aren't counted, which is exactly what you want). MAX only when exactly one value exists per group, as above.

PostgreSQL crosstab (tablefunc extension)

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
  'SELECT user_id, metric, value FROM user_metrics ORDER BY 1, 2',
  'SELECT DISTINCT metric FROM user_metrics ORDER BY 1'
) AS ct(user_id INT, clicks INT, conversions INT, impressions INT);

More concise for known pivot values, but far less portable than CASE WHEN — stick with CASE WHEN for interviews and cross-database code.

Unpivot — Wide to Long

-- Portable: UNION ALL (see SQL UNION & Set Operations)
SELECT product, 'Q1' AS quarter, q1 AS revenue FROM products
UNION ALL
SELECT product, 'Q2', q2 FROM products
UNION ALL
SELECT product, 'Q3', q3 FROM products
UNION ALL
SELECT product, 'Q4', q4 FROM products;

The Template to Memorize

SELECT group_col,
  SUM/COUNT(CASE WHEN category_col = 'val1' THEN metric_col [ELSE 0] END) AS val1,
  SUM/COUNT(CASE WHEN category_col = 'val2' THEN metric_col [ELSE 0] END) AS val2
FROM table GROUP BY group_col;

Interview Drill

Q: Why does the classic pivot pattern use MAX(CASE WHEN ...) instead of just CASE WHEN ... without an aggregate? A: After GROUP BY, the query must collapse potentially many rows per group into one — a bare CASE WHEN expression isn't valid outside an aggregate context once other columns are being grouped; wrapping it in MAX (or SUM/COUNT depending on intent) satisfies the aggregation requirement while still picking out the one relevant per-group value the CASE expression identifies.

Cross-links

[[SQL Aggregates & GROUP BY]] · [[SQL Window Functions]] · [[SQL UNION & Set Operations]]