SQL · Guides · October 30, 2026 · 12 min read

SQL pivot: turn rows into columns (and unpivot), with examples

To pivot in SQL, turn the values of one column into separate columns, one per value. The portable way is conditional aggregation: GROUP BY the row label and write one aggregate per output column, each counting or summing only its own value, SUM(CASE WHEN month = 7 THEN 1 ELSE 0 END) AS jul. It runs on every database. Some engines add a PIVOT operator that writes those columns for you (DuckDB, SQL Server, Oracle, Snowflake, BigQuery); PostgreSQL has the crosstab function instead; MySQL has neither. The reverse, unpivot, turns columns back into rows.

All examples run on the airline database from the Nomto SQL course, in PostgreSQL syntax on DuckDB (the engine of the Nomto SQL room), and every result table is the real output. Snippets for SQL Server and PostgreSQL’s crosstab are marked as such and were not run here.

Long and wide data

A GROUP BY over two columns returns long data: one row per combination. Flights per aircraft model and month:

SELECT a.model,
       date_trunc('month', f.scheduled_departure)::date AS month,
       COUNT(*) AS flights
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
GROUP BY a.model, month
ORDER BY a.model, month
LIMIT 6;

The first six of 24 rows. Long data is what databases, charts and further SQL like best. People reading a report usually want the wide version: one row per model, one column per month. Getting there is the pivot.

Pivot with CASE: the portable way

Group by the row label only, and build each column with an aggregate that looks at one month:

SELECT a.model,
       SUM(CASE WHEN EXTRACT(month FROM f.scheduled_departure) = 7 THEN 1 ELSE 0 END) AS jul,
       SUM(CASE WHEN EXTRACT(month FROM f.scheduled_departure) = 8 THEN 1 ELSE 0 END) AS aug,
       SUM(CASE WHEN EXTRACT(month FROM f.scheduled_departure) = 9 THEN 1 ELSE 0 END) AS sep,
       COUNT(*) AS total
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
GROUP BY a.model
ORDER BY total DESC;

(The schedule runs from 12 July to 10 September, so July and September are partial months.)

How it works: every flight row passes through all three CASE expressions, and each one adds 1 only if the row belongs to its month. GROUP BY a.model then collapses the rows into one per model. A row total is free: COUNT(*) in the same SELECT. The ninth model, the Airbus A320-200, has no flights at all, so the inner join leaves it out; start from aircrafts with a LEFT JOIN if empty rows should appear.

This works unchanged in PostgreSQL, MySQL, SQL Server, Oracle, SQLite and DuckDB (only the month extraction function differs: MONTH() in MySQL and SQL Server). To sum an amount instead of counting, return the value in the THEN branch: SUM(CASE WHEN ... THEN amount ELSE 0 END). The CASE WHEN guide covers the SUM versus COUNT details.

FILTER: the same pivot, shorter

PostgreSQL, DuckDB and SQLite support the standard FILTER clause, which reads better when there are many columns. Because a pivot puts related numbers side by side, you can compute ratios between the new columns in the next step. App events by platform, with conversion rates:

WITH p AS (
  SELECT platform,
         COUNT(*) FILTER (WHERE event_name = 'search')         AS searches,
         COUNT(*) FILTER (WHERE event_name = 'checkout_start') AS checkouts,
         COUNT(*) FILTER (WHERE event_name = 'purchase')       AS purchases
  FROM app_events
  GROUP BY platform
)
SELECT platform, searches, checkouts, purchases,
       ROUND(100.0 * purchases / searches, 1)  AS purchases_per_100_searches,
       ROUND(100.0 * purchases / checkouts, 1) AS checkout_to_purchase_pct
FROM p
ORDER BY platform;

The web converts better at both steps. In long format, the same comparison would need a self join or window functions; after a pivot it is simple arithmetic across columns. (These are raw event counts; the table also contains resent events, which finding duplicates in SQL removes.)

MySQL, SQL Server and Oracle have no FILTER; use SUM(CASE ...) there.

Pivoting values, not counts: MAX(CASE …)

When each cell holds exactly one value, such as an attribute stored as a row, use MAX (or MIN) to pick it. Current fares from Sheremetyevo, one column per class:

SELECT departure_airport, arrival_airport,
       MAX(CASE WHEN fare_conditions = 'Economy'  THEN price END) AS economy,
       MAX(CASE WHEN fare_conditions = 'Comfort'  THEN price END) AS comfort,
       MAX(CASE WHEN fare_conditions = 'Business' THEN price END) AS business
FROM fare_price_history
WHERE valid_to IS NULL
  AND departure_airport = 'SVO'
GROUP BY departure_airport, arrival_airport
ORDER BY business DESC NULLS LAST
LIMIT 5;

Here the CASE has no ELSE, so a missing class comes out as NULL, which is correct: these routes have no Comfort fare (only 10 routes in the whole table do), and a price of 0 would be a lie. With counts it is the other way round: 0 is the right answer for “no flights”, and ELSE 0 or COUNT gives it. MAX works for text and dates too, which makes this the standard way to turn key-value rows (attribute name, attribute value) into columns.

The PIVOT operator in DuckDB

DuckDB has a PIVOT statement. ON names the column whose values become columns, USING the aggregate, GROUP BY the row label. With an explicit IN list you choose the columns and their names:

PIVOT (
  SELECT a.model, EXTRACT(month FROM f.scheduled_departure) AS m
  FROM flights f
  JOIN aircrafts a USING (aircraft_code)
)
ON m IN (7 AS jul, 8 AS aug, 9 AS sep)
USING COUNT(*)
GROUP BY model
ORDER BY model;

The same numbers as the CASE version. Two extras make DuckDB’s PIVOT convenient. Several aggregates at once, each producing a column per value:

PIVOT (
  SELECT f.departure_airport, tf.fare_conditions, tf.amount
  FROM ticket_flights tf
  JOIN flights f USING (flight_id)
  WHERE f.departure_airport IN ('SVO', 'DME', 'VKO')
)
ON fare_conditions IN ('Economy', 'Business')
USING COUNT(*) AS seg, ROUND(SUM(amount)) AS rev
GROUP BY departure_airport
ORDER BY departure_airport;

And, without IN, it finds the columns in the data:

PIVOT flights
ON status
USING COUNT(*)
GROUP BY departure_airport
ORDER BY departure_airport
LIMIT 3;

That is handy for exploring, but note the column On Time with a space in its name, and the fact that the set of columns now depends on the data. More on that below.

PIVOT in SQL Server (and Oracle)

SQL Server’s PIVOT is a clause inside FROM. Not run here; the shape is:

-- SQL Server
SELECT model, [7] AS jul, [8] AS aug, [9] AS sep
FROM (
  SELECT a.model, MONTH(f.scheduled_departure) AS m, f.flight_id
  FROM flights f
  JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
) AS src
PIVOT (
  COUNT(flight_id) FOR m IN ([7], [8], [9])
) AS p;

The trap: there is no GROUP BY. SQL Server groups by every column of the source that is not used in the pivot. That is why the source is a subquery with only model, m and flight_id; select f.* there and you get one row per flight instead of one per model. The IN list takes column names in square brackets, and only one aggregate is allowed. Oracle’s PIVOT looks similar but accepts several aggregates and quoted literals in IN.

PostgreSQL: crosstab

PostgreSQL has no PIVOT. The tablefunc extension provides crosstab, which takes a query returning (row label, category, value) and spreads it out. Not run here:

-- PostgreSQL, needs: CREATE EXTENSION tablefunc;
SELECT *
FROM crosstab(
  $$SELECT a.model::text, EXTRACT(month FROM f.scheduled_departure)::int, COUNT(*)
    FROM flights f
    JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
    GROUP BY 1, 2
    ORDER BY 1, 2$$,
  $$VALUES (7), (8), (9)$$
) AS ct(model text, jul bigint, aug bigint, sep bigint);

You still have to list the output columns and their types in AS ct(...), the source query must be sorted by the row label, and empty cells come back as NULL, not 0. Always use the two-argument form shown here: the one-argument version fills columns left to right and shifts values into the wrong column when a row is missing a category. Most PostgreSQL users find COUNT(*) FILTER (WHERE ...) simpler and stick with it.

Dynamic pivot: when you do not know the columns

A SQL query must declare its output columns before it runs. That is why every method above lists the values (jul, aug, sep) by hand, and why “pivot on all distinct values, whatever they are” is not possible in a plain query in most databases. The options:

• List the values. Best for reports: the columns are stable, named and in a sensible order. If a new status appears in the data, it is silently left out, so add an other column (COUNT(*) FILTER (WHERE status NOT IN (...))) when that matters.
• Generate the SQL. In SQL Server, build the column list from the data with STRING_AGG and run it with sp_executesql; in PostgreSQL, a function with EXECUTE; or build the query in Python or your BI tool.
• Let the engine do it. DuckDB’s PIVOT without IN, Snowflake’s PIVOT ... ANY. Convenient, but the result’s shape changes with the data, which breaks downstream code that expects fixed columns.
• Do not pivot in SQL at all. Return long data and pivot in the spreadsheet, the BI tool or pandas (pivot_table). Often the cleanest split of work.

Unpivot: columns back into rows

Unpivoting turns a wide table into a long one: each (row, column) pair becomes a row with a “name” column and a “value” column. You need it when data arrives as a spreadsheet with a column per month, or to chart a wide table. Start with a wide seat map, one column per class, and unpivot it in DuckDB:

WITH wide AS (
  SELECT aircraft_code,
         COUNT(*) FILTER (WHERE fare_conditions = 'Economy')  AS economy,
         COUNT(*) FILTER (WHERE fare_conditions = 'Comfort')  AS comfort,
         COUNT(*) FILTER (WHERE fare_conditions = 'Business') AS business
  FROM seats
  GROUP BY aircraft_code
)
UNPIVOT wide
ON economy, comfort, business
INTO NAME fare_class VALUE seats
ORDER BY aircraft_code, fare_class
LIMIT 6;

The portable alternative works everywhere: one SELECT per column, glued with UNION ALL.

SELECT aircraft_code, 'economy' AS fare_class, economy AS seats FROM wide
UNION ALL
SELECT aircraft_code, 'comfort', comfort FROM wide
UNION ALL
SELECT aircraft_code, 'business', business FROM wide;

It reads the table once per column. PostgreSQL and DuckDB can do it in one pass with a LATERAL join to a VALUES list (SQL Server writes the same idea as CROSS APPLY (VALUES ...)):

SELECT w.aircraft_code, v.fare_class, v.seats
FROM wide w
CROSS JOIN LATERAL (
  VALUES ('economy', w.economy), ('comfort', w.comfort), ('business', w.business)
) AS v(fare_class, seats)
WHERE w.aircraft_code = '773'
ORDER BY v.seats DESC;

(Both run with the same WITH wide AS (...) as above.) SQL Server also has an UNPIVOT clause: FROM wide UNPIVOT (seats FOR fare_class IN (economy, comfort, business)) AS u.

One difference to watch: DuckDB’s UNPIVOT and SQL Server’s UNPIVOT drop cells that are NULL, so a route with no Comfort fare simply has no Comfort row. UNION ALL and LATERAL VALUES keep them as rows with a NULL value. Decide which one your next step expects.

Which method to use

FAQ

How do I convert rows to columns in SQL?

Group by the column that should stay as rows and write one aggregate per new column, each limited to one value: SUM(CASE WHEN category = 'A' THEN amount ELSE 0 END) AS a. This conditional aggregation works in every SQL database. DuckDB, SQL Server and Oracle also offer a PIVOT operator.

Does PostgreSQL have PIVOT?

No. Use conditional aggregation with FILTER (WHERE ...) or CASE, or the crosstab function from the tablefunc extension.

Does MySQL have PIVOT?

No. MySQL pivots with SUM(CASE ...) or SUM(IF(...)) and GROUP BY. For a column list that depends on the data, build the query with GROUP_CONCAT and run it as a prepared statement.

Can I pivot without knowing the column names in advance?

Not in a single static query in most databases, because output columns must be known before the query runs. Use dynamic SQL, DuckDB’s PIVOT without an IN list, or return long data and pivot in your BI tool or spreadsheet.

What is the difference between PIVOT and GROUP BY?

GROUP BY returns one row per combination of grouping values (long format). A pivot additionally spreads one of those grouping columns across the page as separate columns (wide format). Under the hood a pivot is a GROUP BY with one conditional aggregate per output column.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文