SQL Pivot Table: PIVOT, crosstab and CASE Examples

TL;DR: A pivot table in SQL turns row values into columns, for example one row per month with a column per product. SQL Server, Oracle, Snowflake and BigQuery have a PIVOT operator. PostgreSQL and MySQL do not, so you pivot with conditional aggregation (SUM(...) FILTER (WHERE ...) or SUM(CASE WHEN ...)), or in PostgreSQL with the crosstab() function from the tablefunc extension.

A SQL pivot is a query that rotates distinct values of one column into separate output columns and aggregates the values that land in each cell. The portable way to write it, which works in PostgreSQL, MySQL, SQLite, SQL Server and every warehouse, is one GROUP BY plus one conditional aggregate per output column:

SELECT region,
       SUM(CASE WHEN product = 'Basic' THEN revenue ELSE 0 END) AS basic,
       SUM(CASE WHEN product = 'Pro'   THEN revenue ELSE 0 END) AS pro,
       SUM(CASE WHEN product = 'Team'  THEN revenue ELSE 0 END) AS team
FROM sales
GROUP BY region
ORDER BY region;

Everything else on this page is a variation of that pattern: the native PIVOT syntax in engines that have it, PostgreSQL's FILTER and crosstab(), dynamic column lists, unpivoting, and the mistakes that silently put numbers in the wrong column.

What is a pivot table in SQL?

A pivot table in SQL is a result set where the values of one column (the category) become column headers, one remaining column becomes the row label, and a third column is aggregated into the cells. Spreadsheet users know the same idea from Excel and Google Sheets pivot tables. In SQL the output columns must be known when the query is written, which is the single biggest difference from a spreadsheet pivot and the source of most pivot questions on Stack Overflow and Reddit.

Take this sales table:

region | product | revenue

EU | Basic | 100

EU | Pro | 250

US | Basic | 80

US | Team | 400

US | Pro | 300

Pivoted by product, it becomes:

region | basic | pro | team

EU | 100 | 250 | 0

US | 80 | 300 | 400

Use a pivot when the result is read by a human (a report, a dashboard table, a CSV for finance) and the number of categories is small and stable: months of the year, plan tiers, order statuses. Keep data unpivoted (long format) when it feeds another query, a chart library or a BI tool, because long format is easier to filter, join and aggregate.

Which databases support the PIVOT operator?

The PIVOT keyword is vendor syntax, not part of the core SQL standard, so support varies:

Database | Native PIVOT | How to pivot

SQL Server / Azure SQL | Yes | PIVOT (SUM(x) FOR col IN ([a],[b]))

Oracle (11g and later) | Yes | PIVOT (SUM(x) FOR col IN ('a' AS a, 'b' AS b))

Snowflake | Yes, with ANY and subquery | PIVOT (SUM(x) FOR col IN (ANY ORDER BY col))

BigQuery | Yes | PIVOT (SUM(x) FOR col IN ('a', 'b')) in the FROM clause

PostgreSQL | No | FILTER, CASE, or crosstab() from tablefunc

MySQL / MariaDB | No | SUM(CASE ...) or SUM(IF(...))

SQLite | No | SUM(CASE ...) or SUM(...) FILTER (WHERE ...)

If you need one query that runs on several engines, use conditional aggregation. It is never wrong, it is easy to read, and query planners handle it as a single pass over the table.

How do you pivot with conditional aggregation?

Conditional aggregation means wrapping each output column in an aggregate that only counts rows for one category. It has three parts: the row label in SELECT and GROUP BY, one SUM/COUNT/MAX per category, and the condition inside it.

 Monthly signups per plan (works in PostgreSQL, MySQL 8, SQL Server, SQLite)
SELECT date_trunc('month', created_at) AS month,         , MySQL: DATE_FORMAT(created_at, '%Y-%m-01')
       COUNT(CASE WHEN plan = 'free' THEN 1 END)  AS free,
       COUNT(CASE WHEN plan = 'pro'  THEN 1 END)  AS pro,
       COUNT(CASE WHEN plan = 'team' THEN 1 END)  AS team,
       COUNT(*)                                    AS total
FROM users
GROUP BY 1
ORDER BY 1;

Two details matter:

In MySQL you can shorten CASE to SUM(IF(product = 'Pro', revenue, 0)). Our guide to CASE WHEN in PostgreSQL covers the CASE expression itself in more depth.

How do you pivot in PostgreSQL with FILTER?

PostgreSQL's aggregate `FILTER (WHERE ...)` clause, added in PostgreSQL 9.4, is the cleanest pivot syntax in any engine. Only rows matching the filter are fed to that aggregate:

 Ticket counts per support agent, one column per status
SELECT assignee,
       COUNT(*) FILTER (WHERE status = 'open')     AS open,
       COUNT(*) FILTER (WHERE status = 'pending')  AS pending,
       COUNT(*) FILTER (WHERE status = 'closed')   AS closed,
       AVG(first_reply_minutes) FILTER (WHERE status = 'closed') AS avg_reply_closed
FROM tickets
WHERE opened_at >= now() - interval '30 days'
GROUP BY assignee;

Each line reads like a sentence ("count tickets where status is open"), and you can mix different aggregates per column, which PIVOT syntax cannot do. COUNT returns 0 for no matches, but SUM and AVG return NULL for empty cells; wrap them in COALESCE(..., 0) if a report needs zeros. SQLite also supports FILTER on aggregates (since 3.30.0), so the same query runs there. MySQL and SQL Server do not support FILTER, so stick to CASE for portable code.

What does the crosstab() function do?

crosstab() is a set-returning function in PostgreSQL's `tablefunc` extension that produces a pivot from a three-column query (row name, category, value). Install it once per database. The module is marked "trusted", so any user with CREATE privilege on the database can install it, not just a superuser:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Because crosstab() returns setof record, you must declare the output columns and their types in the FROM clause. Always use the two-argument form, which takes the source query and a second query listing the categories:

SELECT *
FROM crosstab(
  $$ SELECT region, product, SUM(revenue)
     FROM sales
     GROUP BY 1, 2
     ORDER BY 1, 2 $$,
  $$ VALUES ('Basic'), ('Pro'), ('Team') $$
) AS ct(region text, basic numeric, pro numeric, team numeric);

Using dollar quoting ($$ ... $$) avoids doubling every single quote inside the source query, which is what the official examples do with ''att2''.

Why the one-argument crosstab gives wrong answers

The PostgreSQL docs state that one-argument crosstab(sql) "treats all values in a group alike, inserting each value into the first available column." It ignores what the category actually is. In the sample data, EU has no Team row, so its values fill basic and pro correctly by luck, but if EU had Basic and Team and no Pro, the Team revenue would land in the pro column. No error is raised. Extra rows beyond the declared columns are silently dropped, and short groups are padded with NULL.

The two-argument form maps each value to its category, so missing categories become NULL in the right column. The other crosstab() rules from the docs:

When to use crosstab: many categories (12+ columns) where writing one FILTER line each gets long. For 2 to 10 categories, FILTER or CASE is easier to read and review, needs no extension, and works on managed Postgres plans where you cannot add extensions.

What is the SQL Server PIVOT syntax?

SQL Server's PIVOT operator rotates the unique values of one column into multiple columns and aggregates the rest. Microsoft documents it as an alternative to "a complex series of SELECT...CASE statements":

SELECT region, [Basic], [Pro], [Team]
FROM (
    SELECT region, product, revenue
    FROM sales
) AS src
PIVOT (
    SUM(revenue) FOR product IN ([Basic], [Pro], [Team])
) AS pvt
ORDER BY region;

Pivot from a derived table (src) that selects only the columns you need. Every column in the source that is not the aggregated column or the FOR column becomes an implicit grouping column, so an extra column such as id produces one output row per id instead of per region. Microsoft also notes that repeated use of PIVOT/UNPIVOT within a single T-SQL statement can hurt performance.

How do you pivot in Snowflake and BigQuery?

Both warehouses put PIVOT in the FROM clause (BigQuery query syntax).

 BigQuery
SELECT *
FROM (SELECT region, product, revenue FROM dataset.sales)
PIVOT (SUM(revenue) FOR product IN ('Basic', 'Pro', 'Team'));

 Snowflake: pivot on every distinct value, no hard-coded list
SELECT *
FROM (SELECT region, product, revenue FROM sales)
PIVOT (SUM(revenue) FOR product IN (ANY ORDER BY product))
  DEFAULT ON NULL (0);

Snowflake's IN (ANY [ORDER BY ...]) and IN (<subquery>) forms solve the dynamic-column problem natively, and DEFAULT ON NULL (value) replaces empty cells. One documented catch: with ANY, a NULL in the pivot column is treated as its own pivot value, so filter NULLs out first if you do not want a NULL column.

How do you make a dynamic pivot when the columns are not known?

A dynamic pivot is one where the category list comes from the data itself, such as one column per customer or per week. Standard SQL cannot do it in a single static statement because the result's column list must be fixed when the query is parsed. Your options:

Engine | Dynamic option

Snowflake | IN (ANY) or IN (SELECT ...)

SQL Server | Build the column list with STRING_AGG(QUOTENAME(product), ','), then run it with sp_executesql

PostgreSQL | Generate the SQL in a PL/pgSQL function or in application code, then execute it; or return JSON with jsonb_object_agg(product, revenue)

MySQL | GROUP_CONCAT the SUM(CASE ...) fragments, then PREPARE and EXECUTE

The PostgreSQL JSON trick is often the best answer for apps:

SELECT region, jsonb_object_agg(product, total) AS revenue_by_product
FROM (SELECT region, product, SUM(revenue) AS total
      FROM sales GROUP BY 1, 2) t
GROUP BY region;
 EU | {"Pro": 250, "Basic": 100}

The column count stays fixed (two), and the front end spreads the JSON keys into columns. When building SQL strings dynamically, quote identifiers (QUOTENAME in SQL Server, format('%I', ...) or quote_ident() in PostgreSQL) so a category value cannot inject SQL.

Can you reverse a pivot (UNPIVOT)?

Unpivoting is the reverse: turning a wide table (jan, feb, mar columns) into long rows (month, amount). SQL Server, Oracle, Snowflake and BigQuery have UNPIVOT. In PostgreSQL use a LATERAL join over a VALUES list:

 budget(dept, jan, feb, mar) -> one row per dept and month
SELECT b.dept, m.month, m.amount
FROM budget b
CROSS JOIN LATERAL (VALUES ('Jan', b.jan),
                           ('Feb', b.feb),
                           ('Mar', b.mar)) AS m(month, amount);

In MySQL, UNION ALL one SELECT per column. Unpivot is the usual first step when a spreadsheet export lands in the database with one column per month.

What are the common pivot mistakes?

When the next step after a pivot is a running total or a share of total per row, a window function is usually cleaner than a second pivot; see PARTITION BY in SQL.

If you would rather not hand-write the FILTER lines, AI for Database lets you ask "revenue by region with a column per product" in plain English against your PostgreSQL, MySQL or SQL Server database and shows the SQL it generated, so you can check the pivot logic before trusting the numbers.

FAQ

Does PostgreSQL have a PIVOT function?

No. PostgreSQL has no PIVOT keyword. Use SUM(...) FILTER (WHERE ...) per column, SUM(CASE ...), or crosstab() from the tablefunc extension (CREATE EXTENSION tablefunc;).

Does MySQL support PIVOT?

No. MySQL and MariaDB have no PIVOT operator. Use SUM(CASE WHEN col = 'x' THEN val END) or SUM(IF(col = 'x', val, 0)) with GROUP BY, and PREPARE/EXECUTE for dynamic columns.

What is the difference between PIVOT and GROUP BY?

GROUP BY collapses rows into one row per group with values still listed down the page. PIVOT (or conditional aggregation) adds a second dimension by spreading one column's values across the page as columns. Every pivot uses a GROUP BY, explicit or implicit.

Can you pivot without an aggregate function?

Not in PIVOT syntax; the aggregate is required. When each cell has exactly one source row, use MAX(value) or MIN(value), which return that single value and also work for text columns.

How do I pivot multiple columns at once?

With conditional aggregation, add one expression per value and category pair, for example SUM(revenue) FILTER (WHERE product = 'Pro') AS pro_revenue and COUNT(*) FILTER (WHERE product = 'Pro') AS pro_orders. Snowflake and BigQuery also accept multiple aggregates in one PIVOT clause, with an alias appended to each output column name.

Is crosstab faster than CASE in PostgreSQL?

For typical report sizes the difference is small, because both read the table once. crosstab() runs the source query and then reshapes it in C; FILTER does the work inside a single GROUP BY. Measure with EXPLAIN ANALYZE on your data before choosing on speed; choose on correctness and readability first.