how to pivot rows to columns in SQL

TL;DR

Use conditional aggregation: SUM(CASE WHEN category = 'x' THEN value END) AS x for each category, grouped by the row key. It works on every SQL engine, reads clearly, and handles the fixed-category case that covers most reporting pivots.

how to pivot rows to columns in SQL

Use this when

• Long data (one row per category) must become wide (one column per category)
• You are writing a cross-tab or matrix report in SQL
• The categories are known ahead of time

Not for this skill when

• You need columns to become rows (thats unpivot, use UNION ALL)
• You are pivoting in pandas (pivot_table covers that)
• The category list is dynamic and unknown at query time (generate the SQL instead)

Steps

1. Write the pivot with conditional aggregation. One CASE expression per output column:

SELECT region,
       SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1_revenue,
       SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2_revenue,
       SUM(CASE WHEN quarter = 'Q3' THEN revenue END) AS q3_revenue,
       SUM(CASE WHEN quarter = 'Q4' THEN revenue END) AS q4_revenue
FROM sales
GROUP BY region;

Expected output: one row per region with four revenue columns. Non-matching rows contribute NULL, which SUM ignores.

1. Decide what a missing combination should show. NULL vs zero matters downstream:

SELECT region,
       COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN revenue END), 0) AS q1_revenue
FROM sales
GROUP BY region;

Expected output: 0 instead of NULL where a region had no Q1 sales. Use COALESCE when the consumer cant handle NULLs.

1. Pivot counts and distinct counts the same way:

SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(CASE WHEN status = 'won' THEN 1 END) AS won,
       COUNT(CASE WHEN status = 'lost' THEN 1 END) AS lost,
       COUNT(DISTINCT CASE WHEN status = 'won' THEN account_id END) AS won_accounts
FROM deals
GROUP BY 1;

Expected output: monthly won/lost counts plus distinct won accounts. COUNT only counts non-NULL, so the CASE trick filters for free.

1. On Postgres with many categories, use the crosstab function from the tablefunc extension:

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
  'SELECT region, quarter, SUM(revenue) FROM sales GROUP BY 1, 2 ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric, q3 numeric, q4 numeric);

Expected output: the pivoted grid. You must still declare the output columns, and the category query defines their order.

1. On engines with native PIVOT (BigQuery, Snowflake, SQL Server), prefer it for readability:

-- BigQuery
SELECT * FROM sales
PIVOT (SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

Expected output: the same wide result with less typing. The category list is still static; dynamic pivots need generated SQL on every engine.

Variant phrasings

SQL rows to columns transpose

Same technique. "Transpose" usually means the conditional-aggregation pivot from step 1.

postgres pivot without crosstab

Step 1 is the answer. Conditional aggregation needs no extensions and is what most Postgres shops actually use.

dynamic pivot in SQL

Generate the CASE list from SELECT DISTINCT category in your application or a stored procedure that builds the query string. Pure static SQL cant have a variable column list.

Why it happens

SQL result sets have a fixed column list determined before execution, so "one column per distinct value" cant be expressed without naming the values. Conditional aggregation works around this by writing one expression per value you care about, turning the row filter into a column definition. It is verbose but completely portable.

Edge cases

• Categories with special characters need quoted aliases: AS "Q1 (est.)".
• If new categories appear over time, the pivot silently drops them; add a data-quality check comparing DISTINCT categories against your column list.
• Pivoting text values (not numbers): use MAX(CASE WHEN ... THEN name END) to pick the value per group.
• Wide pivots with hundreds of columns get unreadable fast; past a few dozen categories, pivot in the reporting layer instead.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstqaafV8S8EWmVe_nWCzK5g