Pivot without PIVOT

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.

Loading editor…

• 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

Loading editor…

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.