Recipe

Rows to columns (pivot)

Short answer: turn distinct values from a column into multiple columns. Two flavors. The portable form: SUM(CASE WHEN col = 'value' THEN 1 ELSE 0 END) AS value works on every engine, scales to any number of pivoted values, and is easy to read. The native form: PIVOT(...), supported on DuckDB / Snowflake / BigQuery / SQL Server / Databricks; nicer syntax when available.

Canonical SQL #

sql

-- Portable: CASE-based pivot.
SELECT
  user_id,
  SUM(CASE WHEN event_type = 'login'   THEN 1 ELSE 0 END) AS logins,
  SUM(CASE WHEN event_type = 'click'   THEN 1 ELSE 0 END) AS clicks,
  SUM(CASE WHEN event_type = 'logout'  THEN 1 ELSE 0 END) AS logouts
FROM   events
GROUP  BY user_id;

-- DuckDB / Snowflake / BigQuery / SQL Server: native PIVOT.
SELECT *
FROM   events
PIVOT (
  COUNT(*)
  FOR event_type IN ('login', 'click', 'logout')
);

Example #

Loading SQL editor...

Common variants #

sql

-- Pivot a SUM rather than a count.
SELECT
  region,
  SUM(CASE WHEN year = 2024 THEN revenue END) AS revenue_2024,
  SUM(CASE WHEN year = 2025 THEN revenue END) AS revenue_2025,
  SUM(CASE WHEN year = 2026 THEN revenue END) AS revenue_2026
FROM   sales
GROUP  BY region;

-- Pivot with FILTER (cleaner on Postgres / DuckDB / SQLite).
SELECT
  region,
  SUM(revenue) FILTER (WHERE year = 2024) AS revenue_2024,
  SUM(revenue) FILTER (WHERE year = 2025) AS revenue_2025,
  SUM(revenue) FILTER (WHERE year = 2026) AS revenue_2026
FROM   sales
GROUP  BY region;

-- Native PIVOT with explicit value list (DuckDB / Snowflake / BigQuery / SQL Server).
SELECT * FROM sales
PIVOT (SUM(revenue) FOR year IN (2024, 2025, 2026));

-- Dynamic pivot (unknown values at write time): not standard SQL.
-- Build the pivot SQL in application code, or use Snowflake's PIVOT(... FOR col IN (ANY)).

When pivot is the wrong shape #

Pivot output is great for humans reading reports: wide tables with one row per entity and one column per category. It's typically the wrong shape for downstream programmatic consumption: every new category becomes a schema change. If the pivoted result will be consumed by code or another query, leave the data tall (one row per entity-category-value) and let the consuming layer pivot for display. The same logic applies to BI tools, most of which can pivot at render time.

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL	`SUM(x) FILTER (WHERE col = 'a')`	no native `PIVOT`; `crosstab` needs the `tablefunc` extension
MySQL	`SUM(CASE WHEN col = 'a' THEN x END)`	no native `PIVOT`, no `FILTER`
SQL Server	`PIVOT (SUM(x) FOR col IN ([a], [b]))`	value list is bracketed identifiers; dynamic pivot needs dynamic SQL
BigQuery	`PIVOT (SUM(x) FOR col IN ('a', 'b'))`	value list must be explicit
Snowflake	`PIVOT (SUM(x) FOR col IN ('a', 'b'))`	`IN (ANY)` for dynamic value lists
Oracle	`PIVOT (SUM(x) FOR col IN ('a', 'b'))`	11g+; explicit value list
SQLite	`SUM(x) FILTER (WHERE col = 'a')`	`FILTER` needs 3.30+
DuckDB	`PIVOT t ON col USING SUM(x)`	dynamic; discovers the value list automatically

Dialect notes #

Postgres / DuckDB / SQLite (3.30+): SUM(...) FILTER (WHERE ...) is the cleanest portable form. It drops the ELSE 0 and reads naturally. Postgres also has crosstab in the tablefunc extension for true PIVOT.
DuckDB: full ANSI PIVOT (...) with IN ('a','b') value list, PIVOT_LONGER / PIVOT_WIDER aliases, and dynamic pivot PIVOT t ON col USING SUM(amount).
Snowflake: PIVOT (agg(col) FOR pivot_col IN ('a','b')), which requires listing values explicitly. Also PIVOT(... FOR col IN (ANY)) for dynamic.
BigQuery: PIVOT (agg(col) FOR pivot_col IN ('a','b')); value list required.
SQL Server: PIVOT syntax similar to Snowflake. UNPIVOT works the other direction.
MySQL: no native PIVOT. Use the CASE pattern.
Oracle: PIVOT (11g+) with explicit values; XMLAGG for dynamic pivots.

Tip

FILTER (WHERE ...) is the underrated cleanup that turns a hard-to-read pivot into a readable one. Compare SUM(CASE WHEN year = 2025 THEN revenue ELSE 0 END) to SUM(revenue) FILTER (WHERE year = 2025): same result, half the noise. Use it when your engine supports it (Postgres, DuckDB, SQLite). For more on related patterns: PIVOT, Columns to rows (unpivot).