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 0to keep NULL for missing combinations. AddCOALESCE(..., 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 toAVG(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
- Filter before you pivot. Apply
WHEREclauses in the source subquery to cut the rows that reach the aggregation step. - Select only needed columns. Extra columns in a native
PIVOTsource change the grouping and multiply rows. - Cast before unpivoting.
UNPIVOTrequires all source columns to share one data type. Wrap mixed columns inCAST(col AS VARCHAR(50))orCAST(col AS DECIMAL(18,2)). - Decide how to treat NULL. Native
UNPIVOTdrops them. Cross-apply and lateral methods keep them. Choose deliberately. - Sanitize dynamic SQL. Always wrap identifiers with
QUOTENAME(SQL Server) orformat('%I', ...)(PostgreSQL). - 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.
- 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.



