Pivotar filas a columnas con MAX/SUM FILTER en SQL

Un pivote convierte una tabla "larga" (muchas filas por entidad) en una tabla cruzada "ancha", donde cada categoria pasa a ser su propia columna. En PostgreSQL la forma mas limpia de lograrlo es un agregado con la clausula FILTER (WHERE ...), que cuenta solo las filas que cumplen una condicion sin salir de un unico GROUP BY.

El truco central: un agregado por columna

Supongamos que cada fila de orders tiene un estado y queremos los totales por usuario separados por estado en columnas distintas. En vez de varias consultas, escribimos una:

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;

Cada agregado solo "ve" las filas del grupo que pasan su FILTER; el resto se ignora. Funciona con cualquier agregado: SUM, COUNT, MAX, MIN, AVG, array_agg.

MAX FILTER para "elegir el valor por clave"

Cuando los atributos se guardan en vertical (clave-valor), MAX(...) FILTER (...) lleva el valor correcto a la columna correcta. Dadas filas con forma (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;

Aqui MAX es solo una manera de reducir el grupo a un unico valor; si hay exactamente una fila por (user_id, kind), el resultado es justo ese valor.

FILTER frente a SUM(CASE WHEN)

Antes de que existiera FILTER (estandar SQL, PostgreSQL 9.4+) se lograba lo mismo con 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;

Diferencias:

• FILTER se lee mejor y separa con claridad la condicion de la expresion.
• COUNT se comporta distinto: COUNT(*) FILTER (WHERE c) cuenta filas; COUNT(CASE WHEN c THEN 1 END) tambien sirve porque CASE sin ELSE da NULL, que COUNT ignora. El valor que pongas tras THEN da igual: COUNT(CASE WHEN c THEN 0 END) cuenta exactamente las mismas coincidencias, porque COUNT solo ignora la fila cuando toda la expresion es NULL. La verdadera trampa es anadir ELSE: COUNT(CASE WHEN c THEN 1 ELSE 0 END) ya no tiene filas NULL, asi que cuenta todas. Cuando necesites un ELSE, cambia el agregado a SUM(CASE WHEN c THEN 1 ELSE 0 END).
• FILTER forma parte del estandar SQL; CASE es portable casi en todas partes.

Una tabla cruzada real por pais y estado:

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() y sus limites

PostgreSQL incluye crosstab() en la extension tablefunc:

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);

El dolor: la lista de columnas en AS ct(...) hay que declararla a mano de antemano; crosstab() no la deduce. Para un conjunto dinamico de categorias resulta incomodo. El enfoque con FILTER tambien es estatico en columnas, pero es mas simple, no requiere extension y se combina sin friccion con JOIN, HAVING y funciones de ventana.

Trampas y portabilidad

• Cuidado con NULL: si ninguna fila pasa el FILTER, SUM/MAX devuelven NULL. Envuelve con COALESCE(SUM(...) FILTER (...), 0) si quieres cero.
• El numero de columnas queda fijo en el texto de la consulta. Un "pivote dinamico" con categorias desconocidas exige generar SQL en el codigo de la aplicacion o en PL/pgSQL.
• MySQL no tiene FILTER (hasta 8.x) — alli usa SUM(CASE WHEN ... THEN ... END).
• ClickHouse tampoco tiene FILTER, pero ofrece el combinador -If: sumIf(amount, status = 'paid'), countIf(status = 'paid') — un equivalente compacto del pivote.

La regla es simple: para un conjunto estatico de categorias en PostgreSQL, usa FILTER; es la forma mas legible y estandar de convertir filas en columnas en una sola pasada.