Pivot Rows into Columns with MAX/SUM FILTER in SQL

A pivot turns a "tall" table (many rows per entity) into a "wide" cross-tab where each category becomes its own column. In PostgreSQL the cleanest way to do this is an aggregate with the FILTER (WHERE ...) clause, which counts only the rows matching a condition while keeping everything inside a single GROUP BY.

The core trick: one aggregate per column

Say each row in orders has a status, and we want per-user totals split by status into separate columns. Instead of several queries, write one:

SELECT
  user_id,
  SUM(amount) FILTER (WHERE status = 'paid')     AS paid,
  SUM(amount) FILTER (WHERE status = 'pending')   AS pending,
  SUM(amount) FILTER (WHERE status = 'cancelled') AS cancelled,
  COUNT(*) FILTER (WHERE status = 'paid')         AS paid_count
FROM orders
GROUP BY user_id;

Each aggregate only "sees" the rows in the group that pass its FILTER; the rest are ignored. It works with any aggregate: SUM, COUNT, MAX, MIN, AVG, array_agg.

MAX FILTER for "pick the value by key"

When attributes are stored vertically (key-value), MAX(...) FILTER (...) pulls the right value into the right column. Given rows shaped like (user_id, kind, amount):

SELECT
  user_id,
  MAX(amount) FILTER (WHERE kind = 'deposit')    AS deposit,
  MAX(amount) FILTER (WHERE kind = 'withdrawal')  AS withdrawal
FROM tx
GROUP BY user_id;

Here MAX is just a way to collapse the group down to one value; if there is exactly one row per (user_id, kind), the result is simply that single value.

FILTER vs SUM(CASE WHEN)

Before FILTER existed (SQL standard, PostgreSQL 9.4+) you got the same result with CASE:

SELECT
  user_id,
  SUM(CASE WHEN status = 'paid' THEN amount END)      AS paid,
  SUM(CASE WHEN status = 'pending' THEN amount END)    AS pending
FROM orders
GROUP BY user_id;

Differences:

• FILTER reads cleaner and clearly separates the condition from the expression.
• COUNT behaves differently: COUNT(*) FILTER (WHERE c) counts rows; COUNT(CASE WHEN c THEN 1 END) works too because CASE without ELSE yields NULL, which COUNT skips. The value you put after THEN is irrelevant here — COUNT(CASE WHEN c THEN 0 END) counts exactly the same matches, since 0 is still skipped only when the whole expression is NULL. The real trap is adding ELSE: COUNT(CASE WHEN c THEN 1 ELSE 0 END) now has no NULL rows, so it counts every row. When you need an ELSE, switch the aggregate to SUM(CASE WHEN c THEN 1 ELSE 0 END).
• FILTER is part of the SQL standard; CASE is portable almost everywhere.

A real cross-tab by country and status:

SELECT
  u.country,
  COUNT(*) FILTER (WHERE o.status = 'paid')      AS paid,
  COUNT(*) FILTER (WHERE o.status = 'cancelled')  AS cancelled
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.country
ORDER BY u.country;

crosstab() and its limits

PostgreSQL ships crosstab() in the tablefunc extension:

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT user_id, status, sum(amount) FROM orders GROUP BY 1, 2 ORDER BY 1, 2',
  'SELECT DISTINCT status FROM orders ORDER BY 1'
) AS ct(user_id int, paid numeric, pending numeric, cancelled numeric);

The pain: the column list in AS ct(...) must be declared upfront by hand; crosstab() cannot infer it. For a dynamic set of categories that is awkward. The FILTER approach is also static in its columns, but it is simpler, needs no extension, and composes cleanly with JOIN, HAVING, and window functions.

Gotchas and portability

• Mind NULL: if no row passes the FILTER, SUM/MAX return NULL. Wrap with COALESCE(SUM(...) FILTER (...), 0) if you want zero.
• The column count is fixed in the query text. A "dynamic pivot" with unknown categories needs SQL generated in application code or PL/pgSQL.
• MySQL has no FILTER (through 8.x) — use SUM(CASE WHEN ... THEN ... END) there.
• ClickHouse also lacks FILTER, but offers the -If combinator: sumIf(amount, status = 'paid'), countIf(status = 'paid') — a compact pivot equivalent.

The rule is simple: for a static set of categories in PostgreSQL, reach for FILTER — it is the most readable, standard way to turn rows into columns in a single pass.