SQL Pivoting

Transform row data into columns (pivot) or column data into rows (unpivot). SQL has no native PIVOT in standard SQL — use CASE WHEN.

---

Manual Pivot with CASE WHEN

Input: long format — one row per (user, metric)

user_id | metric       | value
--------|--------------|------
1       | clicks       | 10
1       | impressions  | 200
1       | conversions  | 3
2       | clicks       | 15
...

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? After GROUP BY, each group has one row per user. CASE returns the value or NULL; MAX picks the non-NULL value (only one exists per group).

---

Pivot: Count / Sum Pattern

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

---

Pivot: Transpose Rows to Columns (Classic Interview Problem)

Problem: Products table with (product, quarter, revenue). Show one row per product with Q1–Q4 as columns.

SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4
FROM product_revenue
GROUP BY product;

---

PostgreSQL CROSSTAB (tablefunc extension)

More concise for known pivot values:

-- Enable extension (once)
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);

Less portable than CASE WHEN — stick with CASE for interviews.

---

Unpivot — Wide to Long Format

Turn column values back into rows.

-- Given: one row per product with Q1, Q2, Q3, Q4 columns
-- Want: one row per (product, quarter, revenue)

-- PostgreSQL: UNNEST + ARRAY
SELECT product,
  quarter,
  revenue
FROM products
CROSS JOIN UNNEST(
  ARRAY['Q1', 'Q2', 'Q3', 'Q4'],
  ARRAY[q1,   q2,   q3,   q4]
) AS t(quarter, revenue);

-- Portable: UNION ALL
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
ORDER BY product, quarter;

---

DataLemur / Interview Pattern

Problem type: "Show total X per category/status as separate columns"

Template:

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;

Use SUM with ELSE 0 when you need totals.
Use COUNT without ELSE when you need non-null counts (NULL values not counted).
Use MAX when exactly one value exists per group (e.g., pivoting attributes).

---

Related

• [[Aggregates]] — GROUP BY, CASE WHEN in aggregates
• [[Window Functions]] — alternative approach for some pivot-like problems
• [[UNION & Set Operations]] — manual ROLLUP with UNION ALL