UNPIVOT Basics in SQL
Overview
Unpivoting (or melting/normalizing) is the inverse of pivoting: it transforms a wide table (where metrics are spread across multiple columns) into a tall, normalized table (where metrics are stored as row-level key-value pairs). In relational database modeling, wide denormalized tables (such as spreadsheets imported directly into SQL) violate First Normal Form (1NF). Unpivoting restructures these wide datasets into normalized formats required for indexing, relational joins, and dashboard visual aggregations.
What You Will Learn
In this lesson, you will master:
- The Normalization Goal: Converting column headers into categorical row values.
- The Cross-Dialect
UNION ALLMethod: The universal standard for unpivoting across all SQL engines. - Native UNPIVOT Operator: Using SQL Server, Oracle, and Snowflake's
UNPIVOTstatement. - Modern PostgreSQL
CROSS JOIN LATERAL/ Arrays: High-performance multi-column unpivoting. - Handling NULLs during Unpivot: Deciding whether to drop or preserve missing metric cells.
The Transformation Concept
Consider a denormalized spreadsheet-style table imported into SQL:
Wide Source Table (Denormalized):
product_id | q1_sales | q2_sales | q3_sales
-----------+----------+----------+---------
P-100 | 1500 | 2200 | 1800
P-200 | 800 | 950 | 1100
Unpivoted Target Table (Normalized Rows):
product_id | quarter | sales_amount
-----------+----------+-------------
P-100 | Q1 | 1500
P-100 | Q2 | 2200
P-100 | Q3 | 1800
P-200 | Q1 | 800
P-200 | Q2 | 950
P-200 | Q3 | 1100
Method 1: The Universal Cross-Dialect Pattern (UNION ALL)
The standard, portable way to unpivot across any database (PostgreSQL, MySQL, SQLite, DuckDB, Spark SQL) uses UNION ALL:
SELECT product_id, 'Q1' AS quarter, q1_sales AS sales_amount
FROM product_sales_wide
WHERE q1_sales IS NOT NULL
UNION ALL
SELECT product_id, 'Q2' AS quarter, q2_sales AS sales_amount
FROM product_sales_wide
WHERE q2_sales IS NOT NULL
UNION ALL
SELECT product_id, 'Q3' AS quarter, q3_sales AS sales_amount
FROM product_sales_wide
WHERE q3_sales IS NOT NULL
ORDER BY product_id, quarter;
Advantage: Simple, transparent execution plan. Easy to debug.
Limitation: Reads the source table once for every unpivoted column (3 columns = 3 scans of the table).
Method 2: High-Performance Unpivoting with CROSS JOIN LATERAL (PostgreSQL)
In PostgreSQL, you can unpivot in a single table scan using CROSS JOIN LATERAL with VALUES:
SELECT
w.product_id,
u.quarter,
u.sales_amount
FROM product_sales_wide w
CROSS JOIN LATERAL (
VALUES
('Q1', w.q1_sales),
('Q2', w.q2_sales),
('Q3', w.q3_sales)
) AS u(quarter, sales_amount)
WHERE u.sales_amount IS NOT NULL
ORDER BY w.product_id, u.quarter;
This performs a single scan of product_sales_wide, generating the unpivoted rows in memory for each record.
Method 3: Native UNPIVOT Operator (SQL Server, Oracle, Snowflake)
In engines with native UNPIVOT support:
-- SQL Server / Snowflake Syntax
SELECT product_id, quarter, sales_amount
FROM product_sales_wide
UNPIVOT (
sales_amount FOR quarter IN (q1_sales, q2_sales, q3_sales)
) AS unpvt_table;
By default, native UNPIVOT automatically ignores NULL values. If you want to retain nulls, some dialects support UNPIVOT INCLUDE NULLS.
Common Mistakes & Gotchas
- Mismatched Column Data Types: All columns unpivoted into the target value column must have identical data types. If
q1_salesisINTandq2_salesisNUMERIC(10,2), you must cast them explicitly to a common type (CAST(q1_sales AS NUMERIC(10,2))). - Cartesian Duplication: When attempting to unpivot using regular
CROSS JOINinstead ofCROSS JOIN LATERALorUNION ALL, failing to bind row values creates multi-million row duplicate combinations.
Interview Perspective
SQL Interview Question: When and why would you unpivot a dataset, and how can you achieve it in a single table scan in PostgreSQL?
Key Talking Points for Candidates:
- Normalization: Unpivoting normalizes wide spreadsheet-like tables into key-value pairs (tall format) for easier aggregation and relational joins.
- Single Scan Optimization: While
UNION ALLrequires scanning the base table once per unpivoted column, PostgreSQL'sCROSS JOIN LATERAL (VALUES ...)evaluates the transformation in-memory per row during a single table scan. - Data Types: All wide columns collapsed into the single target column must share compatible data types.
Interactive Practice Challenge
DataPlayArena Technical Editorial Board
Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.
Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.