Convert Columns to Rows With UNPIVOT

Reshape a wide table by turning multiple columns into key-value row pairs — the inverse of a PIVOT operation.

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

Docker-validated Not currently validation-green

Example 1

Convert per-quarter sales columns into one row per quarter

The original table has one row per region with three value columns. After unpivoting, each region produces three rows — one per quarter — with a quarter label column and a single revenue value column. The UNION ALL approach reads the source table three times (once per column), which is fine for small tables. The SQL Server UNPIVOT and PostgreSQL LATERAL approaches read the table once.

MySQL MariaDB SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE sales (region VARCHAR(50), q1 INT, q2 INT, q3 INT);

INSERT INTO
  sales
VALUES
  ('East', 100, 120, 140),
  ('West', 80, 95, 110);

Validated query

SQL

SELECT
  region,
  'q1' AS quarter,
  q1 AS revenue
FROM
  sales
UNION ALL
SELECT
  region,
  'q2',
  q2
FROM
  sales
UNION ALL
SELECT
  region,
  'q3',
  q3
FROM
  sales
ORDER BY
  region,
  quarter;

Expected result

region	quarter	revenue
East	q1	100
East	q2	120
East	q3	140
West	q1	80
West	q2	95
West	q3	110

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE sales (region VARCHAR(50), q1 INT, q2 INT, q3 INT);

INSERT INTO
  sales
VALUES
  ('East', 100, 120, 140),
  ('West', 80, 95, 110);

Validated query

SQL

SELECT
  region,
  quarter,
  revenue
FROM
  sales UNPIVOT (revenue FOR quarter IN (q1, q2, q3)) AS u
ORDER BY
  region,
  quarter;

Expected result

region	quarter	revenue
East	q1	100
East	q2	120
East	q3	140
West	q1	80
West	q2	95
West	q3	110

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE sales (region VARCHAR(50), q1 INT, q2 INT, q3 INT);

INSERT INTO
  sales
VALUES
  ('East', 100, 120, 140),
  ('West', 80, 95, 110);

Validated query

SQL

SELECT
  region,
  quarter,
  revenue
FROM
  sales
  CROSS JOIN LATERAL (
    VALUES
      ('q1', q1),
      ('q2', q2),
      ('q3', q3)
  ) AS u (quarter, revenue)
ORDER BY
  region,
  quarter;

Expected result

region	quarter	revenue
East	q1	100
East	q2	120
East	q3	140
West	q1	80
West	q2	95
West	q3	110

SQL Server uses native UNPIVOT. PostgreSQL uses CROSS JOIN LATERAL (VALUES …). MySQL, MariaDB, and SQLite use UNION ALL. All produce an identical six-row result.

Useful when

Where this command helps.

converting a quarterly-columns layout into a time-series of rows for analysis
normalising denormalised multi-column data before loading into a fact table

Explanation

What the command is doing.

UNPIVOT transforms a row with many value columns (e.g. q1, q2, q3) into multiple rows, each containing a column-name label and the corresponding value. SQL Server has native UNPIVOT syntax. PostgreSQL and SQLite use CROSS JOIN LATERAL (VALUES …) to produce the same result without a dedicated keyword. MySQL and MariaDB, which lack lateral joins with correlated value constructors, use UNION ALL to manually stack the columns.

Convert Columns to Rows With UNPIVOT

Convert per-quarter sales columns into one row per quarter

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.