UNPIVOTQuery Techniques
Buy Me A Coffee

Transform columns into rows — the inverse of PIVOT. Takes values spread across multiple columns and normalizes them into a key-value row structure. Useful for reshaping wide, denormalized data.

The inverse of pivot: transforms columns into rows, useful for normalizing denormalized reports back into a normalized form for analysis. SQL Server has native UNPIVOT; other databases use UNION ALL or table-valued constructors.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — No UNPIVOT operator. The universal UNION ALL workaround works in all MySQL versions. In MySQL 8.0+, a JSON-based approach using JSON_TABLE can dynamically unpivot a JSON object into rows.
PostgreSQL ✗ Not Supported — PostgreSQL has no UNPIVOT keyword. The idiomatic approach uses a VALUES clause with a CROSS JOIN LATERAL or a CROSS JOIN (VALUES ...) to enumerate the column-to-row mapping. This is concise and avoids repeating the FROM clause for each union branch.
SQL Server ✓ Supported 2005 UNPIVOT operator available since SQL Server 2005. Syntax mirrors PIVOT: specify the value column name, the key column name, and the list of source columns. UNPIVOT automatically excludes NULL values — rows where the source column is NULL do not appear in the output. For dynamic column lists (unknown at query time), use dynamic SQL.
Oracle ✓ Supported 11g UNPIVOT operator available since Oracle 11g. Supports INCLUDE NULLS / EXCLUDE NULLS clause (EXCLUDE NULLS is the default, unlike the behavior implied by UNION ALL which includes NULLs unless filtered). Can unpivot multiple value columns simultaneously.
SQLite ✗ Not Supported — No UNPIVOT operator. Use the UNION ALL workaround or, in SQLite 3.8.3+, the VALUES table constructor with a CROSS JOIN.

Details

Only SQL Server (2005+) and Oracle (11g+) have a native UNPIVOT operator. The most portable alternative is the UNION ALL pattern — one SELECT per column being unpivoted. For long column lists this becomes verbose; the VALUES + CROSS APPLY/CROSS JOIN approach (SQL Server, PostgreSQL, SQLite 3.8.3+) is more concise and scans the source table only once. SQL Server's UNPIVOT automatically excludes NULLs; Oracle's UNPIVOT defaults to EXCLUDE NULLS but can be changed with INCLUDE NULLS. The CROSS APPLY (VALUES ...) pattern in SQL Server is generally preferred over the UNPIVOT keyword because it handles multiple value columns and is easier to extend.

Standard Syntax

-- SQL Server (UNPIVOT operator): SELECT product_id, quarter, sales FROM quarterly_sales UNPIVOT ( sales FOR quarter IN (q1, q2, q3, q4) ) AS u; -- Universal (UNION ALL approach): SELECT product_id, 'q1' AS quarter, q1 AS sales FROM quarterly_sales UNION ALL SELECT product_id, 'q2', q2 FROM quarterly_sales UNION ALL SELECT product_id, 'q3', q3 FROM quarterly_sales UNION ALL SELECT product_id, 'q4', q4 FROM quarterly_sales;

Version Support

MySQL: Not supported PostgreSQL: Not supported SQL Server: Since 2005 Oracle: Since 11g SQLite: Not supported

Per-Database Syntax & Notes

MySQL

No UNPIVOT operator. The universal UNION ALL workaround works in all MySQL versions. In MySQL 8.0+, a JSON-based approach using JSON_TABLE can dynamically unpivot a JSON object into rows.

-- UNION ALL workaround (works in all versions): SELECT product_id, 'q1' AS quarter, q1 AS sales FROM quarterly_sales WHERE q1 IS NOT NULL UNION ALL SELECT product_id, 'q2', q2 FROM quarterly_sales WHERE q2 IS NOT NULL UNION ALL SELECT product_id, 'q3', q3 FROM quarterly_sales WHERE q3 IS NOT NULL UNION ALL SELECT product_id, 'q4', q4 FROM quarterly_sales WHERE q4 IS NOT NULL ORDER BY product_id, quarter;

PostgreSQL

PostgreSQL has no UNPIVOT keyword. The idiomatic approach uses a VALUES clause with a CROSS JOIN LATERAL or a CROSS JOIN (VALUES ...) to enumerate the column-to-row mapping. This is concise and avoids repeating the FROM clause for each union branch.

-- VALUES + CROSS JOIN (PostgreSQL idiomatic approach): SELECT s.product_id, v.quarter, v.sales FROM quarterly_sales s CROSS JOIN (VALUES ('q1', s.q1), ('q2', s.q2), ('q3', s.q3), ('q4', s.q4) ) AS v(quarter, sales) WHERE v.sales IS NOT NULL; -- UNION ALL alternative (portable): SELECT product_id, 'q1' AS quarter, q1 AS sales FROM quarterly_sales UNION ALL SELECT product_id, 'q2', q2 FROM quarterly_sales UNION ALL SELECT product_id, 'q3', q3 FROM quarterly_sales UNION ALL SELECT product_id, 'q4', q4 FROM quarterly_sales;

SQL Server

UNPIVOT operator available since SQL Server 2005. Syntax mirrors PIVOT: specify the value column name, the key column name, and the list of source columns. UNPIVOT automatically excludes NULL values — rows where the source column is NULL do not appear in the output. For dynamic column lists (unknown at query time), use dynamic SQL.

-- Standard UNPIVOT: SELECT product_id, quarter, sales FROM quarterly_sales UNPIVOT ( sales FOR quarter IN (q1, q2, q3, q4) ) AS unpvt; -- Multiple value columns (unpivot pairs): -- Requires a workaround since UNPIVOT handles one value column; -- use UNION ALL for multi-value unpivots. -- CROSS APPLY alternative (more flexible): SELECT s.product_id, v.quarter, v.sales FROM quarterly_sales s CROSS APPLY (VALUES ('q1', s.q1), ('q2', s.q2), ('q3', s.q3), ('q4', s.q4) ) AS v(quarter, sales) WHERE v.sales IS NOT NULL;

Oracle

UNPIVOT operator available since Oracle 11g. Supports INCLUDE NULLS / EXCLUDE NULLS clause (EXCLUDE NULLS is the default, unlike the behavior implied by UNION ALL which includes NULLs unless filtered). Can unpivot multiple value columns simultaneously.

-- Standard UNPIVOT: SELECT product_id, quarter, sales FROM quarterly_sales UNPIVOT ( sales FOR quarter IN (q1, q2, q3, q4) ); -- Include NULL rows: SELECT product_id, quarter, sales FROM quarterly_sales UNPIVOT INCLUDE NULLS ( sales FOR quarter IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4') ); -- Custom labels for the key column: SELECT product_id, quarter, sales FROM quarterly_sales UNPIVOT ( sales FOR quarter IN (q1 AS 'Q1 Sales', q2 AS 'Q2 Sales', q3 AS 'Q3 Sales', q4 AS 'Q4 Sales') );

SQLite

No UNPIVOT operator. Use the UNION ALL workaround or, in SQLite 3.8.3+, the VALUES table constructor with a CROSS JOIN.

-- UNION ALL workaround: SELECT product_id, 'q1' AS quarter, q1 AS sales FROM quarterly_sales WHERE q1 IS NOT NULL UNION ALL SELECT product_id, 'q2', q2 FROM quarterly_sales WHERE q2 IS NOT NULL UNION ALL SELECT product_id, 'q3', q3 FROM quarterly_sales WHERE q3 IS NOT NULL UNION ALL SELECT product_id, 'q4', q4 FROM quarterly_sales WHERE q4 IS NOT NULL; -- VALUES + JOIN workaround (3.8.3+): SELECT s.product_id, v.quarter, v.sales FROM quarterly_sales s JOIN (VALUES ('q1'), ('q2'), ('q3'), ('q4')) AS v(quarter) ON 1=1 -- cross join via VALUES