Wide and long: pivoting rows to columns and back

Two shapes for the same facts

A revenue-by-city-by-month result can be long, with one row per city per month and three columns, or wide, with one row per city and a column for each month. Long is how a database stores and groups facts. Wide is how a human reads them across a page. Most reporting is the act of turning one into the other, and most of the friction comes from asking SQL to do the human-shaped half.

Long to wide, when you know the columns

The tool is conditional aggregation, which the CASE lesson introduced: one aggregate per output column, each filtered to its slice.

SELECT city,
       sum(amount) FILTER (WHERE month = '2026-01-01') AS jan,
       sum(amount) FILTER (WHERE month = '2026-02-01') AS feb,
       sum(amount) FILTER (WHERE month = '2026-03-01') AS mar
FROM monthly_sales
GROUP BY city;

Or sum(CASE WHEN month = '2026-01-01' THEN amount END) where FILTER is unsupported. A city with no sales in February gets NULL in that column, and coalesce(..., 0) decides whether the report shows a blank or a zero, which is a decision about meaning, not formatting.

Why the columns must be typed by hand

You will want to write “one column per month, whatever months exist”. SQL cannot do it, and the reason is structural rather than a missing feature. A query’s output columns are part of its type, fixed when the query is planned, before any row is read. The engine has to know there will be a column called apr before it can decide how to produce one. Data-driven columns would mean the shape of the result depends on the result, which the planner cannot resolve.

The escape hatches confirm the rule. Postgres’s crosstab() in the tablefunc extension still needs the output column list declared in the call. DuckDB’s PIVOT monthly_sales ON month USING sum(amount) discovers the months, and does it by running a first query to list them and then building the second query from that list; BigQuery’s PIVOT requires the values in the statement. Where a report must pivot on values that change, the usual answer is to pivot in the reporting tool, or to generate the SQL from a query of the distinct values, which is what DuckDB does for you.

Wide to long

The reverse arrives whenever a spreadsheet lands in the database: city, jan, feb, mar as four columns. Grouping by month is impossible in that shape. Unpivot it:

SELECT city, month, amount
FROM wide_sales
CROSS JOIN LATERAL (VALUES ('2026-01-01', jan), ('2026-02-01', feb), ('2026-03-01', mar))
  AS v(month, amount);

Each source row becomes three rows, one per month. The portable version is a UNION ALL of three SELECT city, '2026-01-01', jan FROM wide_sales queries. DuckDB has UNPIVOT wide_sales ON jan, feb, mar INTO NAME month VALUE amount, which reads as what it does.

Why wide is the wrong storage shape

Store the wide table and every new month is an ALTER TABLE to add a column, every query that groups by time has to be rewritten, and the table is mostly NULL for any city that started trading recently. Store long, and a new month is just more rows. The spreadsheet habit of one column per period is exactly the habit the first lesson of this course described as a spreadsheet stopping working, and the fix is the same: long in the table, wide only at the moment of display.

The free path

A pivot table in LibreOffice Calc or Google Sheets performs this operation with a drag, and for a one-off report on a few thousand rows it is the right tool. Export the long result as CSV, open it, and pivot there. Keep the SQL that produced the long result, because the pivot is display and the query is the record of what the numbers mean.

The habit

Write the query long, check the numbers long, and pivot last, in the query only when the column list is fixed, otherwise in the tool that shows the result. A wide result whose columns you had to type is a report; a wide table somebody stores is a problem waiting for its thirteenth month.