Pivot without PIVOT

Turn row values into columns with conditional aggregation — one query that runs the same on every engine.

The problem

You have one row per (customer, status) and you want one row per customer with a column for each status. Some engines have PIVOT; its syntax differs everywhere and SQLite / MySQL don't have it at all.

The pattern: CASE inside an aggregate

One SUM(CASE …) per output column. GROUP BY the thing you want as rows.

SQL playground

Loading editor…

⌘/Ctrl + Enter

Use SUM for money/counts, MAX for "did this row exist" flags, COUNT for tallies.
ELSE 0 keeps sums numeric; ELSE NULL (the default) is fine for MAX.
COUNT(CASE WHEN … THEN 1 END) counts matches — the ELSE is left out on purpose so non-matches are NULL and skipped.

Counting instead of summing

SQL playground

Loading editor…

⌘/Ctrl + Enter

Filtered aggregates (Postgres, SQLite 3.30+)

Cleaner than CASE where supported:

SELECT
  customer_id,
  SUM(amount) FILTER (WHERE status = 'paid')     AS paid,
  SUM(amount) FILTER (WHERE status = 'refunded') AS refunded
FROM orders
GROUP BY customer_id;

Gotchas

The columns are hard-coded. You have to know the values in advance. Dynamic pivots need generated SQL or a client-side crosstab.
Unpivot (columns back to rows) is the reverse: UNION ALL one SELECT per source column, or CROSS JOIN a small values list.

CASE — the branching expression doing the work.
GROUP BY — sets the row grain of the pivot.
SUM / COUNT — the aggregates wrapping the CASE.