How to Pivot Rows to Columns in SQL

The portable way to pivot rows to columns in SQL is conditional aggregation: one CASE WHEN per target column, wrapped in an aggregate, with GROUP BY on the row key. It works in every database; SQL Server and Oracle offer a PIVOT shortcut (modern warehouses like Snowflake, BigQuery, and DuckDB have one too).

How Do You Pivot With CASE WHEN and GROUP BY?

Aggregate a CASE expression per output column so each category's values land in its own column. This pattern runs unchanged on Postgres, MySQL, SQL Server, Oracle, SQLite, and every major warehouse.

-- sales: region | quarter | amount
SELECT
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,
    SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,
    SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,
    SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4
FROM sales
GROUP BY region;
-- region | q1   | q2   | q3   | q4
-- East   | 1200 | 900  | 1500 | 1100
-- West   | 800  | 1300 | 700  | 1600

Use ELSE 0 for sums you want zero-filled, or omit the ELSE to get NULL for missing combinations. More on the syntax in CASE WHEN statements.

What Is the Native PIVOT Syntax?

SQL Server and Oracle provide a PIVOT operator that does the same thing more tersely, but the column list is still hardcoded.

-- SQL Server / Oracle
SELECT region, [Q1] AS q1, [Q2] AS q2, [Q3] AS q3, [Q4] AS q4
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
    SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS p;
-- Oracle uses quoted strings: FOR quarter IN ('Q1' AS q1, ...)

Feed PIVOT a subquery with only the key, spreading, and value columns — extra columns silently create extra groups.

How Do You Pivot in Postgres?

Postgres has no PIVOT; use conditional aggregation (above, or the shorter FILTER form) or the crosstab() function from the tablefunc extension.

-- Postgres FILTER form (cleaner than CASE):
SELECT region,
       SUM(amount) FILTER (WHERE quarter = 'Q1') AS q1,
       SUM(amount) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales GROUP BY region;

-- crosstab needs: CREATE EXTENSION tablefunc;
SELECT * FROM crosstab(
  $$SELECT region, quarter, SUM(amount) FROM sales
    GROUP BY 1, 2 ORDER BY 1, 2$$
) AS ct(region text, q1 numeric, q2 numeric, q3 numeric, q4 numeric);

Common Pitfalls

• Dynamic categories: every pivot needs the output columns known up front. New categories require editing the query (or generating it dynamically).
• Forgetting the aggregate: CASE WHEN without SUM/MAX around it won't collapse rows — you get one sparse row per input row.
• crosstab and missing cells: plain crosstab() shifts values left when a category is absent; use the two-query form crosstab(source, categories) to pin columns.
• NULL vs 0: SUM over no matching rows gives NULL unless you wrote ELSE 0; that matters for downstream math.