PIVOT and UNPIVOT in DuckDB: From Spreadsheet to Analysis

A spreadsheet is easy to read when every month gets a column. It is harder to analyze when your SQL has to change each time another month arrives. Turning months into values gives you one column to filter, group, and compare.

We will move six spreadsheet cells into rows, preserve the unknown one, and turn the result back into a report. Watch what happens to February before copying a query.

Explore the idea

Follow the blank cell through the reshape

North’s February value is unknown. South’s March value is a measured zero.

region–month cells

Two regions, three month columns

The spreadsheet is easy to scan, but month is encoded in the column names.

Read the cells, including the blank

monthly_sales2 rows

A familiar spreadsheet shape

North February is unknown. South March is a real zero. Each region has one source row.

id	region	jan	feb	mar
1	North	100	NULL	140
2	South	50	70	0

Run along, with the data included

Each Run example button opens an editable SQL shell with its own copy of this sample data. Run the examples in any order. The engine downloads on your first run; sample queries execute in your browser.

Create these tables yourself: full setup SQL

CREATE OR REPLACE TABLE monthly_sales(id INTEGER, region VARCHAR, jan INTEGER, feb INTEGER, mar INTEGER);
INSERT INTO monthly_sales VALUES (1,'North',100,NULL,140), (2,'South',50,70,0);
CREATE OR REPLACE VIEW long_sales AS
SELECT region, month, amount FROM monthly_sales
UNPIVOT INCLUDE NULLS (amount FOR month IN (jan, feb, mar));

Download the complete SQL walkthrough

North has no recorded February amount. South has a recorded March amount of zero. Those facts are different: one is unknown; the other says nothing was sold.

Each region has exactly one source row, and each month column contains the same kind of measurement. Mixing dates, prices, and counts into one unpivoted value column would need a different model.

Stack the month columns

DuckDB’s simplified UNPIVOT names the source columns and the two destination columns:

The simplified form omits the unknown February value

UNPIVOT monthly_sales ON jan, feb, mar
INTO NAME month VALUE amount
ORDER BY region, month;

Expected result · sample data5 rows

id	region	month	amount
1	North	jan	100
1	North	mar	140
2	South	feb	70
2	South	jan	50
2	South	mar	0

Scroll for more columns →

month contains the source column name, and amount contains its value. The unchanged id and region columns accompany each resulting row.

Count the result: five rows, not six. The default unpivot behavior omits NULL values, so North’s unknown February cell disappears. That may be appropriate for a sparse list of known measurements. It is wrong if you need to audit missing measurements.

The alphabetical output order is deliberate for this inspection: feb, jan, mar. Month names are text, not dates. Store an actual month date or a numeric month index when chronological calculations matter.

Preserve unknown values explicitly

Use the SQL-standard form with INCLUDE NULLS:

Keep all six cells, including the unknown one

SELECT region, month, amount FROM monthly_sales
UNPIVOT INCLUDE NULLS (amount FOR month IN (jan, feb, mar))
ORDER BY region, month;

Expected result · sample data6 rows

region	month	amount
North	feb	NULL
North	jan	100
North	mar	140
South	feb	70
South	jan	50
South	mar	0

All six cells now have rows. North February remains NULL, while South March remains zero. The setup saves this result as long_sales, ready for ordinary SQL filters and aggregates.

This format makes questions such as “which region-month measurements are missing?” straightforward. It also lets a new month arrive as more rows rather than a new query expression.

For a changing wide input, DuckDB also supports selecting unpivot columns with a COLUMNS expression. Use that when the naming convention is dependable; blindly selecting every non-key column can sweep unrelated metadata into your measurements.

Build a report with a stable shape

Choose a stable column order for the report

PIVOT long_sales ON month IN ('jan', 'feb', 'mar')
USING sum(amount) GROUP BY region ORDER BY region;

Expected result · sample data2 rows

region	jan	feb	mar
North	100	NULL	140
South	50	70	0

Scroll for more columns →

PIVOT turns the month values into columns. The explicit IN list fixes their order as January, February, March. GROUP BY region fixes the row grain, while sum(amount) decides how multiple contributing rows would combine.

The report still has NULL for North February and zero for South March. Because this fixture has one measurement per region-month, the sums reproduce the known values. With multiple source rows, pivoting aggregates them; it is not a lossless undo button.

Your turn · check your answer

Find the missing measurement

Return region and month for the unknown measurement in long_sales. Do not include a recorded zero.

See the expected result

Your target result1 row

region	month
North	feb

Your SQL

Sample data is loaded for you.

Preparing the editor…

Need a hint?

NULL and zero express different facts. Test the original amount with IS NULL.

Show a solution

SELECT region, month FROM long_sales WHERE amount IS NULL ORDER BY region, month;

Keep analysis and presentation separate

Long-form rows are convenient for filtering and joining. A wide report is convenient for scanning. You can use both without forcing every downstream calculation to work with a growing list of month columns.

Before adding a “total” column, decide what to do when one month is unknown. sum ignores NULL, so a partial sum can look like a complete quarter. Pair it with an observed-month count, or withhold the quarterly result until every required measurement is present.

The moving-average tutorial carries this distinction into a daily time series, where absent rows and unknown values can change the denominator.