intermediate

UNPIVOT Basics in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

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 ALL Method: The universal standard for unpivoting across all SQL engines.
  • Native UNPIVOT Operator: Using SQL Server, Oracle, and Snowflake's UNPIVOT statement.
  • 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:

sql
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:

sql
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
-- 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_sales is INT and q2_sales is NUMERIC(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 JOIN instead of CROSS JOIN LATERAL or UNION 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:

  1. Normalization: Unpivoting normalizes wide spreadsheet-like tables into key-value pairs (tall format) for easier aggregation and relational joins.
  2. Single Scan Optimization: While UNION ALL requires scanning the base table once per unpivoted column, PostgreSQL's CROSS JOIN LATERAL (VALUES ...) evaluates the transformation in-memory per row during a single table scan.
  3. Data Types: All wide columns collapsed into the single target column must share compatible data types.

Interactive Practice Challenge

Challenge: Unpivot Monthly Inventory Levels

You have a table inventory_snapshot with columns sku_id, jan_qty, feb_qty, and mar_qty. Write a query using UNION ALL that unpivots this table into three columns: sku_id, snapshot_month ('Jan', 'Feb', 'Mar'), and stock_qty. Exclude rows where the quantity is NULL.

DPA

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.