Pivoting Rows Into Columns in PostgreSQL With FILTER

Excel users reach for a pivot table without thinking about it, and then meet a database that has no PIVOT keyword. PostgreSQL turns rows into columns with plain aggregates, and once you have seen the pattern it is short. This post builds a cross-tab from a long table, shows crosstab() as the alternative, and covers the case where the set of columns changes. It is a spoke of pivot tables and their SQL equivalents.

The long form and the wide form

A long table has one row per fact:

A wide form has one row per month and one column per product:

Databases store the long form because it is easier to extend and index. People read the wide form because it fits a screen and a chart. Pivoting is the conversion.

Conditional aggregates with FILTER

Group by the row key, and compute one aggregate per column, each restricted to its own value:

SELECT month,
       SUM(units) FILTER (WHERE product = 'Pen') AS pen,
       SUM(units) FILTER (WHERE product = 'Pad') AS pad
FROM sales
GROUP BY month
ORDER BY month;

  month  | pen | pad
---------+-----+-----
 2025-01 |  10 |   4
 2025-02 |  12 |
 2025-03 |     |   7

For each month, the first aggregate sums only the Pen rows, the second only the Pad rows. A month with no Pen row sums an empty set, which is null. FILTER is standard SQL and works in PostgreSQL since 9.4. The older form is SUM(CASE WHEN product = 'Pen' THEN units END), which behaves the same and travels to databases without FILTER.

Replace the null with zero only when a zero is true. For units sold, "no Pen row" does mean zero sold. For a measured value like average temperature, a missing reading is not zero:

COALESCE(SUM(units) FILTER (WHERE product = 'Pen'), 0) AS pen

Use the aggregate that fits the question. COUNT(*) FILTER (WHERE ...) counts rows per column, MAX(...) FILTER (WHERE ...) picks a single value per cell when each cell has at most one row, which is how you turn a key-value table into columns.

Key-value tables to columns

A settings table with (user_id, key, value) is the same problem:

SELECT user_id,
       MAX(value) FILTER (WHERE key = 'theme')  AS theme,
       MAX(value) FILTER (WHERE key = 'locale') AS locale
FROM user_settings
GROUP BY user_id;

MAX is a harmless way to pick the one value when there is only one per key. If the data can contain duplicates, MAX hides them; check with a HAVING COUNT(*) > 1 query first.

crosstab from tablefunc

For many columns the repeated FILTER clauses get long. The tablefunc extension adds crosstab, which pivots a sorted three-column result:

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  $$ SELECT month, product, SUM(units)
     FROM sales
     GROUP BY month, product
     ORDER BY month, product $$,
  $$ VALUES ('Pad'), ('Pen') $$
) AS ct(month text, pad bigint, pen bigint);

The first argument returns row key, category and value, sorted by key then category. The second is a query returning the categories in the order you want the columns. The AS ct(...) list names and types the output; it must match the number and order of categories, or values land in the wrong column. Two cautions: the extension needs privileges to install, and the typed column list is the same fixed list you would have written with FILTER. It saves typing for twenty columns and costs readability for three. For short pivots prefer FILTER.

When the columns change

SQL needs the output columns at the time the query is parsed, so no single static query can add a column when a new product appears. Choices:

1. Generate the SQL. Read the distinct values, build the SELECT string and run it. In psql, \gexec runs generated statements:

SELECT format(
  'SELECT month, %s FROM sales GROUP BY month ORDER BY month',
  string_agg(format('SUM(units) FILTER (WHERE product = %L) AS %I', product, product), ', ')
)
FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p
\gexec

%L quotes a literal and %I quotes an identifier, so a product named O'Brien or Pen Set cannot break or inject into the statement. Always generate with format, never by pasting strings.

1. Return the long form and pivot in the client. pandas pivot_table, Excel itself, or a BI tool all adapt to new categories at no cost. This is often the best split: the database groups, the client rotates.

2. Return JSON. jsonb_object_agg(product, units) gives one object per month with a key per product, which an application can unpack without fixed columns.

SELECT month, jsonb_object_agg(product, units) AS units_by_product
FROM (SELECT month, product, SUM(units) AS units FROM sales GROUP BY month, product) s
GROUP BY month;

Totals and checks

A pivot usually needs a total column and row. Add SUM(units) AS total beside the filtered ones for the row total, and GROUP BY ROLLUP (month) for the column total (writing the same Excel pivot table as a GROUP BY query).

Check the pivot by summing every product column and comparing with SELECT SUM(units) FROM sales. If the numbers differ, a product exists in the data that no FILTER mentions: a new product, a typo or a different capitalization. Add a catch-all column computed as total - pen - pad while developing, and it will show what is left over.

Performance

A pivot with ten FILTER aggregates scans the table once, not ten times, so it is as cheap as a single GROUP BY. Index the grouping and filter columns if the table is large, and keep the output small. Pivoting a billion rows into a thousand columns produces a result that no client wants; summarize first.

Testing a pivot query

A pivot query has a failure mode that a plain aggregate lacks: a value that matches no column. Add a test that sums the output columns and compares the result with a plain total. Run it after each data load, and let it fail the job when they differ. The test costs one extra query and turns a silently short report into an alert. If the product list is generated, test that the generated column count equals the number of distinct products, so a truncated list is caught.

It also helps to keep a small fixture with three months, two products and one missing combination, and to assert the exact output. The fixture documents what the query is supposed to do with a gap, which is the question a reviewer will ask first. Because the pivot logic is arithmetic over a handful of rows, the test is quick to write and rarely needs to change.

When pivoting in PostgreSQL is not the right place

The audience uses a spreadsheet or BI tool. Give them the long form. They can pivot with a click and the result adapts.

The columns are unbounded. Hundreds of products as columns make an unreadable table. Rank and show the top ten with an "Other" column.

You are chasing a layout. A report with merged headers and subtotals in the right places is a rendering task, not a query task (building a multi-sheet Excel report).

The values are not additive. Pivoting averages or distinct counts needs care. Pivot the sums and counts, then divide.

PostgreSQL documents the syntax on its pages for aggregate expressions and the tablefunc module. If the result needs to go to Excel, see sending database results to Excel. To format a long query, use the SQL formatter.