SQL - PIVOT / UNPIVOT

Overview

Pivot rotates rows into columns (e.g., sums per category as separate columns). Some engines have a PIVOT operator; otherwise use conditional aggregation with CASE/SUM. UNPIVOT does the reverse: columns become rows.

At a glance

For portability, prefer conditional aggregation (SUM/CASE) over engine-specific PIVOT.
Beware of dynamic category lists; you may need dynamic SQL to generate column lists.
UNPIVOT can be emulated with UNION ALL across columns.

-- Simulate pivot via conditional aggregation
SELECT
  customer_id,
  SUM(CASE WHEN p.category='Electronics' THEN oi.qty*oi.price ELSE 0 END) AS Electronics,
  SUM(CASE WHEN p.category='Furniture'  THEN oi.qty*oi.price ELSE 0 END) AS Furniture,
  SUM(CASE WHEN p.category='Grocery'    THEN oi.qty*oi.price ELSE 0 END) AS Grocery
FROM orders o
JOIN order_items oi ON oi.order_id=o.id
JOIN products p ON p.id=oi.product_id
GROUP BY customer_id;

-- Unpivot-like: use UNION ALL across columns
SELECT customer_id,'Electronics' AS category,
  SUM(CASE WHEN p.category='Electronics' THEN oi.qty*oi.price ELSE 0 END) AS total
FROM ... GROUP BY customer_id
UNION ALL
SELECT customer_id,'Furniture',
  SUM(CASE WHEN p.category='Furniture' THEN oi.qty*oi.price ELSE 0 END)
FROM ... GROUP BY customer_id;

-- PIVOT example
SELECT customer_id, [Electronics], [Furniture], [Grocery]
FROM (
  SELECT o.customer_id, p.category, (oi.qty*oi.price) AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id=o.id
  JOIN products p ON p.id=oi.product_id
) src
PIVOT (
  SUM(revenue) FOR category IN ([Electronics],[Furniture],[Grocery])
) AS p;

-- UNPIVOT example
SELECT customer_id, category, revenue
FROM (
  SELECT customer_id, [Electronics], [Furniture], [Grocery]
  FROM ... -- pivoted table
) p
UNPIVOT (
  revenue FOR category IN ([Electronics],[Furniture],[Grocery])
) AS u;

-- Use conditional aggregation for pivot
SELECT
  o.customer_id,
  SUM(CASE WHEN p.category='Electronics' THEN oi.qty*oi.price ELSE 0 END) AS Electronics,
  SUM(CASE WHEN p.category='Furniture'  THEN oi.qty*oi.price ELSE 0 END) AS Furniture,
  SUM(CASE WHEN p.category='Grocery'    THEN oi.qty*oi.price ELSE 0 END) AS Grocery
FROM orders o
JOIN order_items oi ON oi.order_id=o.id
JOIN products p ON p.id=oi.product_id
GROUP BY o.customer_id;

Live Pivot (SQLite via SUM/CASE)

Live demo (SQLite)

customer_id	Electronics	Furniture	Grocery
1	2048.99	0	0
2	0	299	14.99
3	14.99	499	0

Sample Use Cases (2)

1) Monthly revenue by category (pivot)

Live demo (SQLite)

ym	Electronics	Furniture	Grocery
2024-05	2063.98	798	14.99

2) Unpivot simulation (category totals as rows)

Live demo (SQLite)

category	revenue
Electronics	2063.98
Furniture	798
Grocery	14.99

« Prev: SQL - Recursive CTE Next: SQL - JSON »