Pivot Rows Into Columns

Turn distinct row values into separate columns using `CASE WHEN` inside an aggregate.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Show each region's revenue in separate Q1–Q4 columns

Each CASE WHEN quarter = 'Q1' THEN revenue END returns the revenue only for Q1 rows and NULL for all others. Wrapping it in SUM collapses the per-group values into a single number — NULL contributions are ignored by SUM. GROUP BY region reduces all four quarter rows per region into a single output row. Adding more columns means adding more CASE WHEN expressions; no engine-specific PIVOT clause is needed.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE quarterly_sales (
  region VARCHAR(20),
  quarter VARCHAR(5),
  revenue INT
);

INSERT INTO
  quarterly_sales
VALUES
  ('North', 'Q1', 100),
  ('North', 'Q2', 150),
  ('North', 'Q3', 120),
  ('North', 'Q4', 200),
  ('South', 'Q1', 80),
  ('South', 'Q2', 90),
  ('South', 'Q3', 110),
  ('South', 'Q4', 130);

Validated query Shared across supported engines.

SQL

SELECT
  region,
  SUM(
    CASE
      WHEN quarter = 'Q1' THEN revenue
    END
  ) AS q1,
  SUM(
    CASE
      WHEN quarter = 'Q2' THEN revenue
    END
  ) AS q2,
  SUM(
    CASE
      WHEN quarter = 'Q3' THEN revenue
    END
  ) AS q3,
  SUM(
    CASE
      WHEN quarter = 'Q4' THEN revenue
    END
  ) AS q4
FROM
  quarterly_sales
GROUP BY
  region
ORDER BY
  region;

Expected result Returned rows for the shared example.

region	q1	q2	q3	q4
North	100	150	120	200
South	80	90	110	130

The CASE WHEN pivot syntax is identical across all supported engines.

Useful when

Where this command helps.

turning quarterly sales rows into a single row per region with one column per quarter
building a cross-tab report without exporting data to a spreadsheet

Explanation

What the command is doing.

SQL stores data in rows, but reports often need it spread across columns — one column per category, quarter, or status. The standard portable technique is to wrap a CASE WHEN expression inside SUM (or MAX) and group by the remaining dimensions. Each CASE WHEN acts as a filter that passes the value through only for matching rows and returns NULL otherwise; the aggregate then collapses those per-group values into a single number. The column list is fixed at query-write time, so the pattern works across every major engine without engine-specific PIVOT syntax.

Pivot Rows Into Columns

Show each region's revenue in separate Q1–Q4 columns

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.