Aggregation

How do you pivot rows into columns in PostgreSQL?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

PostgreSQL has no PIVOT keyword. Write one aggregate per column instead, each with its own FILTER: count(*) FILTER (WHERE status = 'delivered').

One line per column you want, a GROUP BY for the rows, and nothing to install. Most answers to this question reach for the crosstab function, which needs an extension enabled and a column definition list written out by hand; the last two sections cover when that is worth it and when no static query can help at all.

Every example reads from two tables: orders, one row per order with a status, and customers, one row per customer with a country. Both are below.

You can edit and run two of the queries below in your browser, and there are two exercises at the end. See our other guides to aggregating query output for the rest of this family.

Schema · ecommerce2 tables? = nullable

customers

idinteger

nametext

emailtext

city?text

countrytext

created_attimestamptz

is_activeboolean

orders

idinteger

customer_id?integer

ordered_attimestamptz

statustext

total_amountnumeric

SELECT status, count(*) AS orders
FROM orders
GROUP BY status
ORDER BY status;

status	orders
cancelled	11
delivered	161
pending	11
shipped	17

four order statuses in our sandbox, one row each. A pivot turns these four rows into four columns

Does PostgreSQL have a PIVOT keyword?

No, and the parser rejects it before the query runs. Microsoft documents SQL Server's PIVOT operator as rotating a column's values into columns of their own. Here is that operator against a Postgres table:

SELECT * FROM orders
PIVOT (sum(total_amount) FOR status IN ('pending'));

PostgreSQL responds syntax error at or near "("

The message names a token, not the feature, because PostgreSQL stopped parsing as soon as the grammar ran out. Which token it names depends on what surrounds it, so the wording above belongs to this query and not to PIVOT in general.

SELECT * FROM orders
PIVOT (sum(total_amount) FOR status IN ('pending'));

How do you pivot with FILTER?

Write one aggregate per column, each with a FILTER naming the category it counts, and group by whatever should become the rows. Four order statuses become four columns:

SELECT c.country,
       count(*) FILTER (WHERE o.status = 'delivered') AS delivered,
       count(*) FILTER (WHERE o.status = 'shipped')   AS shipped,
       count(*) FILTER (WHERE o.status = 'pending')   AS pending,
       count(*) FILTER (WHERE o.status = 'cancelled') AS cancelled,
       count(*) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country IN ('US', 'GB', 'DE')
GROUP BY c.country
ORDER BY c.country;

country	delivered	shipped	pending	cancelled	total
DE	6	2	0	0	8
GB	22	0	3	3	28
US	47	4	3	3	57

three countries as rows, four order statuses as columns, and the row total beside them

FILTER restricts which rows reach that one aggregate, so each column counts its own subset while the GROUP BY stays the same. The total column is a plain count(*) with no filter, which is the check that the parts add up. Germany shows zeros for two statuses because no German order has them.

Try it: add sum(o.total_amount) FILTER (WHERE o.status = 'delivered') AS delivered_value to the select list and run it again. A pivot can carry more than one measure.

SELECT c.country,
       count(*) FILTER (WHERE o.status = 'delivered') AS delivered,
       count(*) FILTER (WHERE o.status = 'shipped')   AS shipped,
       count(*) FILTER (WHERE o.status = 'pending')   AS pending,
       count(*) FILTER (WHERE o.status = 'cancelled') AS cancelled,
       count(*) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country IN ('US', 'GB', 'DE')
GROUP BY c.country
ORDER BY c.country;

That works for any aggregate, not only count. Money per status, same shape:

SELECT c.country,
       sum(o.total_amount) FILTER (WHERE o.status = 'delivered') AS delivered_value,
       sum(o.total_amount) FILTER (WHERE o.status = 'cancelled') AS cancelled_value
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country IN ('US', 'GB', 'DE')
GROUP BY c.country
ORDER BY c.country;

country	delivered_value	cancelled_value
DE	3774.99	NULL
GB	13148.00	1397.99
US	32840.91	1747.00

the same three countries with a sum per status instead of a count. Germany has no cancelled orders, so that cell is null, not zero

How do you do the same thing with CASE WHEN?

Put a CASE inside each aggregate instead of a FILTER beside it. The rows that fail the test produce null, and count skips nulls, so the totals match:

SELECT c.country,
       count(CASE WHEN o.status = 'delivered' THEN 1 END) AS delivered,
       count(CASE WHEN o.status = 'shipped'   THEN 1 END) AS shipped,
       count(CASE WHEN o.status = 'pending'   THEN 1 END) AS pending,
       count(CASE WHEN o.status = 'cancelled' THEN 1 END) AS cancelled,
       count(*) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.country IN ('US', 'GB', 'DE')
GROUP BY c.country
ORDER BY c.country;

country	delivered	shipped	pending	cancelled	total
DE	6	2	0	0	8
GB	22	0	3	3	28
US	47	4	3	3	57

the same pivot written with CASE, returning the same numbers as the FILTER version above

Both queries return the same three rows and the same numbers in all five columns. FILTER is shorter and says what it means. The CASE form is worth knowing because it is built entirely out of ordinary expressions, so it survives being pasted into an engine whose aggregate syntax you have not checked.

When should you use the crosstab function instead?

When the query is genuinely a report generator and the extension is available to you. crosstab is part of the tablefunc extension, which ships with PostgreSQL and is switched on per database with CREATE EXTENSION tablefunc;.

Three things about it are worth weighing before you reach for it, all from that documentation. It takes a query returning three columns, in the order row name, category, value. It returns SETOF record, so every call needs a column definition list written out after AS, which means the column names are still typed by hand. And the two-argument form takes a second query supplying the category list, which is the part people hope will make the pivot dynamic.

For a handful of known columns, FILTER is less code and needs no privileges. crosstab earns its place when the row name and category come from data you are already selecting and the column list is long.

How do you pivot when you do not know the categories in advance?

Not in one static statement. A SQL query has to know its result columns before it runs, so no arrangement of FILTER, CASE or crosstab can invent a column from a value it has not read yet. Pages that present crosstab as the dynamic answer are describing its second query argument, which still requires the column definition list to match.

Two honest routes exist and both leave SQL for a moment. Query the distinct categories first, then build the pivot statement as text in your application and run it. Or write a PL/pgSQL function that returns SETOF record and assembles the same statement server-side, which moves the string building rather than removing it. If the consumer is a spreadsheet or a BI tool, pivoting there is usually less work than either.

Practice: turning categories into columns

The FILTER clause is node N049 of our free SQL course, and putting a CASE inside an aggregate is N027. Both teach the technique, not the pivot, and grouping is N014. No account, nothing to install, nothing to pay.