Pivoting and Unpivoting Data in SQL

Raw transactional tables store one fact per row. Dashboards, machine learning models, and finance reports want one entity per row with metrics spread across columns. Pivoting converts rows into columns. Unpivoting does the reverse. Mastering both lets you reshape data inside the database, before it ever reaches Pandas, Power BI, or a feature store.

Quick Takeaways

  • Pivot turns distinct row values into columns using aggregation. Unpivot turns columns back into rows (wide to long).
  • Conditional aggregation (SUM(CASE WHEN ...)) is the only approach that works on every SQL engine.
  • Native PIVOT and UNPIVOT operators exist in SQL Server, Snowflake, Oracle, and BigQuery. PostgreSQL and MySQL do not have them.
  • Pivot column lists must be known at parse time. Use dynamic SQL when the values change.
Task Portable method Native shortcut
Rows → Columns SUM(CASE WHEN ...) PIVOT (SQL Server, Snowflake, BigQuery, Oracle)
Columns → Rows UNION ALL or LATERAL (VALUES ...) UNPIVOT (SQL Server, Snowflake, BigQuery, Oracle)
Unknown column list Dynamic SQL Dynamic SQL

What Pivoting and Unpivoting Mean

A pivot groups rows by one or more key columns, then spreads the distinct values of a pivot column across new column headers. An aggregate function (SUM, COUNT, AVG, MAX) fills each cell.

An unpivot takes several columns with the same meaning (such as q1, q2, q3, q4) and stacks them into two columns: a name column holding the old header and a value column holding the measurement.

Shape Also called Best for
Long (tall) Tidy, normalized, narrow Storage, joins, time-series, GROUP BY analysis, plotting libraries
Wide Denormalized, cross-tab Reports, spreadsheets, ML feature matrices, executive dashboards

The sample dataset

Every example below uses this table:

CREATE TABLE sales (
    region   VARCHAR(20),
    quarter  CHAR(2),
    revenue  DECIMAL(12,2)
);

INSERT INTO sales (region, quarter, revenue) VALUES
('North', 'Q1', 12000), ('North', 'Q2', 15000), ('North', 'Q3', 11000), ('North', 'Q4', 18000),
('South', 'Q1',  9000), ('South', 'Q2',  9500), ('South', 'Q3', 13000),
('West',  'Q1', 20000), ('West',  'Q2', 17500), ('West',  'Q3', 21000), ('West', 'Q4', 24000);

Note that South has no Q4 row. This gap shows how each method treats missing combinations.

How to Pivot Data in SQL

Method 1: Conditional aggregation (works everywhere)

This is the most portable pattern. Each output column filters the rows it cares about.

SELECT
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1_revenue,  -- NULL if no match
    SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2_revenue,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue END) AS q3_revenue,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue END) AS q4_revenue
FROM sales
GROUP BY region
ORDER BY region;

Key details:

  • Omit ELSE 0 to keep NULL for missing combinations. Add COALESCE(..., 0) only when zero is the correct business value.
  • The query scans the table once, so cost stays at O(n) regardless of how many pivot columns you add.
  • You can mix aggregates freely, such as COUNT(CASE ...) next to AVG(CASE ...).

Method 2: PostgreSQL FILTER clause

PostgreSQL offers cleaner syntax for the same logic.

SELECT
    region,
    SUM(revenue) FILTER (WHERE quarter = 'Q1') AS q1_revenue,
    SUM(revenue) FILTER (WHERE quarter = 'Q2') AS q2_revenue,
    SUM(revenue) FILTER (WHERE quarter = 'Q3') AS q3_revenue,
    SUM(revenue) FILTER (WHERE quarter = 'Q4') AS q4_revenue
FROM sales
GROUP BY region;

Method 3: PostgreSQL crosstab()

The tablefunc extension ships a crosstab function. It needs a source query ordered by row key and a category query that fixes the column order.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    $$ SELECT region, quarter, SUM(revenue)
       FROM sales
       GROUP BY region, quarter
       ORDER BY region, quarter $$,
    $$ VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4') $$
) AS ct (region TEXT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC);

You must declare output column types manually. That makes crosstab more brittle than FILTER, so reserve it for cases where you already use the extension.

Method 4: Native PIVOT in SQL Server

SELECT region, [Q1], [Q2], [Q3], [Q4]
FROM (
    SELECT region, quarter, revenue      -- source: only the columns you need
    FROM sales
) AS src
PIVOT (
    SUM(revenue)                          -- aggregate function
    FOR quarter IN ([Q1], [Q2], [Q3], [Q4])  -- pivot column and its values
) AS pvt;

Warning: PIVOT groups by every column in the source that is not the aggregated or pivoted column. Select only the columns you want. An extra column such as sale_id silently breaks the grouping and returns one row per ID.

Method 5: Native PIVOT in Snowflake and BigQuery

-- Snowflake
SELECT *
FROM sales
PIVOT (SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))
     AS p (region, q1, q2, q3, q4);

-- BigQuery
SELECT *
FROM sales
PIVOT (SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

Snowflake also accepts FOR quarter IN (ANY) for dynamic pivots, which removes the need for string-built SQL.

Dynamic pivot in SQL Server

When the pivot values change, build the column list at runtime. Use QUOTENAME to block SQL injection.

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',') WITHIN GROUP (ORDER BY quarter)
FROM (SELECT DISTINCT quarter FROM sales) AS q;

SET @sql = N'
SELECT region, ' + @cols + N'
FROM (SELECT region, quarter, revenue FROM sales) AS src
PIVOT (SUM(revenue) FOR quarter IN (' + @cols + N')) AS pvt;';

EXEC sys.sp_executesql @sql;

How to Unpivot Data in SQL

Assume the pivoted result is saved as quarterly_sales (region, q1, q2, q3, q4).

Method 1: Native UNPIVOT (SQL Server, Oracle, BigQuery)

SELECT region, quarter, revenue
FROM quarterly_sales
UNPIVOT (
    revenue FOR quarter IN (q1, q2, q3, q4)   -- value column FOR name column IN (source columns)
) AS u;

Behavior to remember: UNPIVOT drops rows where the value is NULL. South’s missing Q4 disappears. If you need those rows, use the LATERAL/APPLY method below. Snowflake and BigQuery support an INCLUDE NULLS option.

Method 2: CROSS APPLY (VALUES ...) in SQL Server

This version preserves NULLs and lets you unpivot several column groups at once.

SELECT qs.region, v.quarter, v.revenue
FROM quarterly_sales AS qs
CROSS APPLY (VALUES
    ('Q1', qs.q1),
    ('Q2', qs.q2),
    ('Q3', qs.q3),
    ('Q4', qs.q4)
) AS v (quarter, revenue);

Method 3: LATERAL (VALUES ...) in PostgreSQL

SELECT qs.region, v.quarter, v.revenue
FROM quarterly_sales AS qs
CROSS JOIN LATERAL (VALUES
    ('Q1', qs.q1),
    ('Q2', qs.q2),
    ('Q3', qs.q3),
    ('Q4', qs.q4)
) AS v (quarter, revenue);

This is usually the fastest portable unpivot because it reads the base table once.

Method 4: UNION ALL (universal fallback)

SELECT region, 'Q1' AS quarter, q1 AS revenue FROM quarterly_sales
UNION ALL
SELECT region, 'Q2', q2 FROM quarterly_sales
UNION ALL
SELECT region, 'Q3', q3 FROM quarterly_sales
UNION ALL
SELECT region, 'Q4', q4 FROM quarterly_sales;

It works on MySQL, SQLite, and every older engine. The cost is one table scan per column, so avoid it on wide tables with millions of rows.

Method 5: Snowflake UNPIVOT

SELECT region, quarter, revenue
FROM quarterly_sales
UNPIVOT INCLUDE NULLS (revenue FOR quarter IN (q1, q2, q3, q4));

Snowflake uppercases unquoted identifiers, so the quarter values come back as Q1, Q2, and so on.

Pivot vs. Unpivot Methods Compared

Method Portability Handles NULLs Dynamic columns Performance Readability
SUM(CASE ...) All engines Full control Needs dynamic SQL Single scan High
FILTER PostgreSQL, SQLite Full control Needs dynamic SQL Single scan Very high
crosstab() PostgreSQL only Fills gaps with NULL Fixed types required Good Low
Native PIVOT SQL Server, Oracle, Snowflake, BigQuery NULL for gaps Snowflake ANY only Optimized Medium
Native UNPIVOT SQL Server, Oracle, Snowflake, BigQuery Drops NULLs by default No Optimized Medium
LATERAL/APPLY PostgreSQL, SQL Server, Oracle Keeps NULLs No Single scan Medium
UNION ALL All engines Keeps NULLs No n scans High

Engine support at a glance

Engine PIVOT UNPIVOT Recommended pivot Recommended unpivot
SQL Server Yes Yes PIVOT / conditional aggregation CROSS APPLY (VALUES)
PostgreSQL No No FILTER LATERAL (VALUES)
MySQL No No SUM(CASE ...) UNION ALL
Snowflake Yes Yes PIVOT ... (ANY) UNPIVOT INCLUDE NULLS
BigQuery Yes Yes PIVOT UNPIVOT
Oracle Yes Yes PIVOT UNPIVOT

Real-World Use Cases

Use case 1: Churn features from event logs

An events table holds one row per user action. A churn model needs one row per user with behavioral counts. Pivot with conditional aggregation:

SELECT
    user_id,
    COUNT(*) FILTER (WHERE event_type = 'login')         AS logins_30d,
    COUNT(*) FILTER (WHERE event_type = 'support_ticket') AS tickets_30d,
    COUNT(*) FILTER (WHERE event_type = 'purchase')       AS purchases_30d,
    MAX(event_ts) FILTER (WHERE event_type = 'login')     AS last_login_ts
FROM events
WHERE event_ts >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id;

The output drops straight into scikit-learn or XGBoost as a feature matrix. Use COALESCE on counts only if your pipeline cannot accept NULL.

Use case 2: Sensor readings to tidy format

IoT tables often store one column per sensor. Time-series tools and anomaly detectors expect a long format with a metric column.

SELECT r.ts, v.metric, v.value
FROM readings AS r
CROSS JOIN LATERAL (VALUES
    ('temp_c',       r.temp_c),
    ('humidity_pct', r.humidity_pct),
    ('pressure_hpa', r.pressure_hpa)
) AS v (metric, value)
WHERE v.value IS NOT NULL;   -- drop failed sensor reads

You can now compute rolling z-scores per metric with a single window function: AVG(value) OVER (PARTITION BY metric ORDER BY ts ROWS 59 PRECEDING).

Use case 3: Executive revenue cross-tab

Finance teams want months across the top and product lines down the side. A pivot with ROLLUP adds totals in one pass:

SELECT
    COALESCE(region, 'ALL REGIONS') AS region,
    SUM(revenue) FILTER (WHERE quarter = 'Q1') AS q1,
    SUM(revenue) FILTER (WHERE quarter = 'Q2') AS q2,
    SUM(revenue) FILTER (WHERE quarter = 'Q3') AS q3,
    SUM(revenue) FILTER (WHERE quarter = 'Q4') AS q4,
    SUM(revenue)                                AS total
FROM sales
GROUP BY ROLLUP (region)
ORDER BY region;

SQL vs. Pandas: Reshaping Equivalents

If your team reshapes in Python, these functions map one-to-one:

SQL operation Pandas function Notes
Pivot with aggregate df.pivot_table(index, columns, values, aggfunc) Use pivot() only when keys are unique
Unpivot df.melt(id_vars, value_vars, var_name, value_name) Keeps NULLs by default
Dynamic pivot pivot_table Columns adapt automatically
import pandas as pd

# Pivot: long -> wide
wide = sales.pivot_table(
    index="region", columns="quarter", values="revenue",
    aggfunc="sum", fill_value=None
).reset_index()

# Unpivot: wide -> long
long = wide.melt(
    id_vars="region", var_name="quarter", value_name="revenue"
).dropna(subset=["revenue"])

Reshape in SQL when the table holds more than a few million rows, because the database avoids moving raw data across the network. Reshape in Pandas when you need to iterate on shape during exploration.

Best Practices and Common Pitfalls

  1. Filter before you pivot. Apply WHERE clauses in the source subquery to cut the rows that reach the aggregation step.
  2. Select only needed columns. Extra columns in a native PIVOT source change the grouping and multiply rows.
  3. Cast before unpivoting. UNPIVOT requires all source columns to share one data type. Wrap mixed columns in CAST(col AS VARCHAR(50)) or CAST(col AS DECIMAL(18,2)).
  4. Decide how to treat NULL. Native UNPIVOT drops them. Cross-apply and lateral methods keep them. Choose deliberately.
  5. Sanitize dynamic SQL. Always wrap identifiers with QUOTENAME (SQL Server) or format('%I', ...) (PostgreSQL).
  6. Cap pivot width. Pivoting a column with thousands of distinct values creates thousands of columns. Most engines limit columns per table (SQL Server allows 1,024 per table, with 4,096 for wide tables using sparse columns). Bucket or filter first.
  7. Store long, serve wide. Keep the normalized table as your source of truth. Create pivoted views or materialized views for reporting.

FAQ

What is the difference between PIVOT and UNPIVOT in SQL?

PIVOT rotates distinct row values into columns and applies an aggregate function such as SUM or COUNT. UNPIVOT rotates multiple columns into rows, producing a name column and a value column. Pivot is lossy because the aggregate collapses duplicates. Unpivot is not an exact inverse of pivot unless the original had one row per key.

How do I pivot data in MySQL or PostgreSQL without a PIVOT operator?

Use conditional aggregation. In MySQL, write SUM(CASE WHEN col = 'X' THEN value END) for each target column and add GROUP BY. In PostgreSQL, use SUM(value) FILTER (WHERE col = 'X'), which is shorter and equally fast. For many columns, generate the SQL with a script or format() inside a function.

How do I create a dynamic pivot when column values change?

Build the column list from SELECT DISTINCT, concatenate it into a query string, and execute it with sp_executesql (SQL Server) or EXECUTE (PostgreSQL PL/pgSQL). Always escape identifiers with QUOTENAME. In Snowflake, use PIVOT (... FOR col IN (ANY)) and skip the string building.

Why does UNPIVOT remove my NULL values?

The native UNPIVOT operator excludes rows where the value column is NULL by design. To keep them, use CROSS APPLY (VALUES ...) in SQL Server, CROSS JOIN LATERAL (VALUES ...) in PostgreSQL, or add INCLUDE NULLS in Snowflake and BigQuery.

Hot this week

GDPR for Data Scientists: Your Responsibilities Explained

Learn what GDPR requires from data scientists: lawful basis, minimization, pseudonymization, DSARs, Article 22. Get code and a checklist.

Funnel Analysis in SQL: Find Where Users Drop Off

Learn funnel analysis in SQL with copy-paste queries for ordered steps, drop-off rates, and segments. Build your conversion funnel today.

Cohort and Retention Analysis in SQL

Learn cohort and retention analysis in SQL with copy-paste PostgreSQL queries, retention matrices, and Python heatmaps. Build yours today.

Polars for Pandas Users: A Practical Migration Guide

Migrate from Pandas to Polars with side-by-side code, expressions, and lazy evaluation. Follow this guide and speed up your pipelines today.

SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Master SQL time series analysis: date functions, rolling averages, and gap filling with copy-ready PostgreSQL code. Start querying smarter today.

Topics

GDPR for Data Scientists: Your Responsibilities Explained

Learn what GDPR requires from data scientists: lawful basis, minimization, pseudonymization, DSARs, Article 22. Get code and a checklist.

Funnel Analysis in SQL: Find Where Users Drop Off

Learn funnel analysis in SQL with copy-paste queries for ordered steps, drop-off rates, and segments. Build your conversion funnel today.

Cohort and Retention Analysis in SQL

Learn cohort and retention analysis in SQL with copy-paste PostgreSQL queries, retention matrices, and Python heatmaps. Build yours today.

Polars for Pandas Users: A Practical Migration Guide

Migrate from Pandas to Polars with side-by-side code, expressions, and lazy evaluation. Follow this guide and speed up your pipelines today.

SQL for Time Series: Date Functions, Rolling Averages, and Gaps

Master SQL time series analysis: date functions, rolling averages, and gap filling with copy-ready PostgreSQL code. Start querying smarter today.

Build a RAG App From Scratch: Documents, Embeddings, and Answers

Build a RAG app from scratch in Python: chunk documents, create embeddings, retrieve context, and generate cited answers. Copy the full code now.

SHAP vs LIME: Explain Any Model’s Predictions

Compare SHAP vs LIME for explaining ML predictions. See code, speed tradeoffs, and a decision guide. Pick the right explainer for your model today.

Predict House Prices With Linear Regression: A Complete Project

Build a house price predictor with linear regression in Python. Clean data, train, and evaluate with RMSE and R². Start the full project now.

Related Articles

Popular Categories