UNPIVOT
UNPIVOT reshapes wide data by turning multiple columns into rows, converting a set of columns into name/value pairs -- the inverse of PIVOT.
Overview
UNPIVOT transforms "wide" data, where related values are spread across multiple columns, into "long" data, where those same values live in fewer columns with an extra column identifying which original column each value came from. It's the inverse operation of PIVOT, and DuckDB implements both the ANSI SQL standard syntax and a simplified, DuckDB-friendly syntax.
Simplified DuckDB syntax
Copy code
UNPIVOT monthly_sales
ON jan, feb, mar, apr, may, jun
INTO
NAME month
VALUE sales;
Given a table with one row per product and a column per month, this produces one row per (product, month) pair, with a month column holding the original column name and a sales column holding that month's value.
SQL standard syntax
Copy code
FROM monthly_sales
UNPIVOT (
sales
FOR month IN (jan, feb, mar, apr, may, jun)
);
This is equivalent to the simplified form above -- sales is the value column, month is the name column, and jan...jun are the source columns being stacked.
Unpivoting multiple value columns at once
DuckDB additionally supports unpivoting several related columns together, useful when a wide table stores multiple metrics per period:
Copy code
SELECT product_id, quarter, sales, returns
FROM quarterly_metrics
UNPIVOT (
(sales, returns)
FOR quarter IN (
(q1_sales, q1_returns) AS 'Q1',
(q2_sales, q2_returns) AS 'Q2'
)
);
Handling NULLs
By default, UNPIVOT drops rows where the value being unpivoted is NULL. Add INCLUDE NULLS to keep them:
Copy code
FROM monthly_sales
UNPIVOT INCLUDE NULLS (sales FOR month IN (jan, feb, mar));
Why UNPIVOT matters for analytics
Long-format data is generally easier to aggregate, filter, and plot -- most charting libraries and GROUP BY queries expect one row per observation rather than one column per category. UNPIVOT is the direct SQL alternative to reshaping data in pandas (melt) or other tools, and DuckDB's friendlier syntax makes it approachable for ad hoc use directly in SQL.
Related terms
A wide table is a table with a large number of columns, typically produced by denormalizing and flattening related data together, common in analytics and columnar data warehouses.
column →A column represents a single field or attribute in a database table or DataFrame that contains values of the same data type.
Generated column →A generated column is a table column whose value is automatically computed from an expression involving other columns, rather than being stored directly by INSERT or UPDATE statements.
PIVOT clause →The PIVOT clause is a SQL feature that transforms rows into columns, making it easier to create summary tables and cross-tabulations of your data.
USING clause →The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.
NATURAL JOIN →A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.
FAQS
UNPIVOT converts columns into rows (wide to long), while PIVOT does the opposite, converting distinct row values into columns (long to wide).
By default no -- NULL values are dropped; add INCLUDE NULLS to keep them in the output.
Yes -- DuckDB's syntax supports unpivoting multiple related columns together, such as unpivoting both a sales and a returns column across the same quarterly periods in one statement.
