Skip to content
GreenSnippets

Pivot rows into columns with FILTER

Turn row values into columns (for example monthly totals) using conditional aggregates, without crosstab.

SQL
SELECT
  customer_id,
  SUM(amount) FILTER (WHERE status = 'paid')     AS paid,
  SUM(amount) FILTER (WHERE status = 'pending')  AS pending,
  SUM(amount) FILTER (WHERE status = 'refunded') AS refunded,
  SUM(amount)                                    AS total
FROM invoices
GROUP BY customer_id
ORDER BY total DESC;

Example / expected output

Output
-- customer_id  paid   pending  refunded  total
-- 7            1200       300      NULL   1500

How it works

Conditional aggregation is portable and easy to read. In PostgreSQL the FILTER clause is the cleanest syntax; on other databases use SUM(CASE WHEN ... THEN amount END). The set of columns must be known when you write the query.

This snippet has been reviewed but was not executed by an automated test. Test it in your own environment before relying on it.

Related snippets