SQL Pivot and Unpivot Queries

TL;DR

• Bottom line: Pivot (rows to columns) and unpivot (columns to rows) are solved differently on every major database engine; pick the right pattern for your platform.
• Key tool/command: SELECT ... PIVOT (AGG(val) FOR col IN (...)) on SQL Server/Oracle; crosstab() on PostgreSQL; SUM(CASE WHEN ...) everywhere.
• Watch out for: SQL Server UNPIVOT silently drops NULLs — wrap columns in ISNULL() first.
• Works with: PostgreSQL 8.3+, SQL Server 2005+, Oracle 11g+, MySQL 5.0+ (CASE-based), all versions of MariaDB and SQLite.

Constraints

• PostgreSQL crosstab() requires CREATE EXTENSION tablefunc — fails with "function crosstab does not exist" without it
• Native PIVOT/UNPIVOT (SQL Server, Oracle) only works with a fixed, compile-time list of column values — dynamic columns require dynamic SQL
• MySQL and SQLite have no PIVOT/UNPIVOT syntax — use conditional aggregation (CASE/IF + GROUP BY) instead
• SQL Server UNPIVOT silently discards rows where the source column is NULL — always use ISNULL(col, 0) or COALESCE before unpivoting
• Oracle UNPIVOT defaults to EXCLUDE NULLS — add INCLUDE NULLS if you need to preserve them
• PostgreSQL crosstab() requires input sorted by the row-name column — unsorted input produces incorrect or missing values

Quick Reference

Decision Tree

START
├── Direction: PIVOT (rows → columns)?
│   ├── YES → Column values known at query-write time?
│   │   ├── YES (static pivot) → Which database?
│   │   │   ├── SQL Server / Oracle → Use native PIVOT clause
│   │   │   ├── PostgreSQL → Use FILTER() (9.4+) or crosstab()
│   │   │   └── MySQL / SQLite / Any → Use SUM(CASE WHEN ... END) + GROUP BY
│   │   └── NO (dynamic pivot) → Which database?
│   │       ├── SQL Server → Build PIVOT in dynamic SQL (sp_executesql)
│   │       ├── MySQL → GROUP_CONCAT + PREPARE/EXECUTE
│   │       └── PostgreSQL → PL/pgSQL function with EXECUTE
│   └── NO ↓
├── Direction: UNPIVOT (columns → rows)?
│   ├── YES → Need to preserve NULLs?
│   │   ├── YES → Which database?
│   │   │   ├── SQL Server → Use CROSS APPLY (VALUES ...) instead of UNPIVOT
│   │   │   ├── Oracle → Use UNPIVOT INCLUDE NULLS
│   │   │   └── PostgreSQL → Use CROSS JOIN LATERAL (VALUES ...)
│   │   └── NO (NULLs can be dropped) → Which database?
│   │       ├── SQL Server / Oracle → Use native UNPIVOT clause
│   │       ├── PostgreSQL → Use CROSS JOIN LATERAL (VALUES ...)
│   │       └── Any → Use UNION ALL (one SELECT per column)
│   └── NO ↓
└── DEFAULT → If columns are truly unknown at runtime, consider pivoting in
    the application layer (pandas pivot_table, spreadsheet, BI tool)

Step-by-Step Guide

1. Identify pivot direction and column values

Determine whether you need pivot (rows to columns) or unpivot (columns to rows). List all distinct values that will become column headers. [src3]

-- Find distinct values that will become columns
SELECT DISTINCT category FROM sales ORDER BY category;
-- Result: 'Electronics', 'Clothing', 'Food'

Verify: Count the distinct values — this is your output column count. If > 20, consider a different approach.

2. Write the pivot query for your database engine

Choose the pattern from the Quick Reference table matching your database and static/dynamic requirement. [src1] [src2]

-- PostgreSQL: FILTER approach (recommended for PostgreSQL 9.4+)
SELECT
  sale_year,
  SUM(amount) FILTER (WHERE category = 'Electronics') AS electronics,
  SUM(amount) FILTER (WHERE category = 'Clothing')    AS clothing,
  SUM(amount) FILTER (WHERE category = 'Food')        AS food
FROM sales
GROUP BY sale_year
ORDER BY sale_year;

Verify: SELECT COUNT(DISTINCT category) FROM sales; → should match the number of pivoted columns

3. Handle NULLs and missing combinations

Pivot results will have NULLs where no data exists for a row/column combination. Decide whether to show NULL or a default. [src2]

-- Wrap in COALESCE for zero-fill
SELECT
  sale_year,
  COALESCE(SUM(amount) FILTER (WHERE category = 'Electronics'), 0) AS electronics,
  COALESCE(SUM(amount) FILTER (WHERE category = 'Clothing'), 0)    AS clothing,
  COALESCE(SUM(amount) FILTER (WHERE category = 'Food'), 0)        AS food
FROM sales
GROUP BY sale_year;

Verify: Check for unexpected NULLs — SELECT * FROM <pivot_result> WHERE electronics IS NULL;

4. Test with edge cases

Verify the query with: (a) a category with no data, (b) NULL values in the pivot column, (c) duplicate rows. [src4]

-- Insert test edge case: NULL category
INSERT INTO sales (sale_year, category, amount) VALUES (2025, NULL, 100);
-- Re-run pivot — NULL category should not corrupt other columns

Verify: Row count of pivoted result should equal COUNT(DISTINCT row_identifier) from the source.

Code Examples

PostgreSQL: Static Pivot with crosstab()

-- Input:  sales table with (sale_year, quarter, revenue) rows
-- Output: one row per year, columns for Q1-Q4

-- Step 1: Enable extension (once per database)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Step 2: Pivot using crosstab
-- IMPORTANT: source query MUST be sorted by the row-name column (sale_year)
SELECT *
FROM crosstab(
  $$SELECT sale_year, quarter, SUM(revenue)
    FROM sales
    GROUP BY sale_year, quarter
    ORDER BY sale_year, quarter$$,   -- sorted by row-name
  $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$
) AS ct(sale_year INT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC);

PostgreSQL: Static Pivot with FILTER (preferred, no extension)

-- Input:  sales table with (region, product, revenue)
-- Output: one row per region, one column per product category
SELECT
  region,
  SUM(revenue) FILTER (WHERE product = 'Widget')  AS widget_rev,
  SUM(revenue) FILTER (WHERE product = 'Gadget')  AS gadget_rev,
  SUM(revenue) FILTER (WHERE product = 'Gizmo')   AS gizmo_rev,
  COUNT(*)     FILTER (WHERE product = 'Widget')   AS widget_cnt
FROM sales
GROUP BY region
ORDER BY region;
-- FILTER is ~8% faster than CASE on PostgreSQL 15+ benchmarks

SQL Server: PIVOT and UNPIVOT

-- PIVOT: rows to columns
SELECT customer_id, [Q1], [Q2], [Q3], [Q4]
FROM (
  SELECT customer_id, quarter, total_amount
  FROM orders
) AS src
PIVOT (
  SUM(total_amount)
  FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt
ORDER BY customer_id;

-- UNPIVOT: columns back to rows
-- WARNING: UNPIVOT drops rows where value is NULL
SELECT customer_id, quarter, total_amount
FROM quarterly_report
UNPIVOT (
  total_amount FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS unpvt;

MySQL: CASE-Based Pivot (no native PIVOT)

-- Input:  exam_scores(student_id, subject, score)
-- Output: one row per student, columns for each subject
SELECT
  student_id,
  MAX(CASE WHEN subject = 'Math'    THEN score END) AS math,
  MAX(CASE WHEN subject = 'Science' THEN score END) AS science,
  MAX(CASE WHEN subject = 'English' THEN score END) AS english,
  MAX(CASE WHEN subject = 'History' THEN score END) AS history
FROM exam_scores
GROUP BY student_id
ORDER BY student_id;
-- Use MAX for single values, SUM for aggregation

SQL Server: Dynamic Pivot with sp_executesql

-- Dynamic columns based on distinct product_name values
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- Build column list (QUOTENAME prevents SQL injection)
SELECT @columns = STRING_AGG(QUOTENAME(product_name), ', ')
FROM (SELECT DISTINCT product_name FROM product_sales) AS products;

SET @sql = N'
SELECT region, ' + @columns + N'
FROM (SELECT region, product_name, revenue FROM product_sales) AS src
PIVOT (SUM(revenue) FOR product_name IN (' + @columns + N')) AS pvt
ORDER BY region;';

EXEC sp_executesql @sql;

PostgreSQL: Unpivot with LATERAL VALUES

-- Input:  quarterly_report(customer_id, q1, q2, q3, q4)
-- Output: one row per customer per quarter (preserves NULLs)
SELECT
  r.customer_id,
  u.quarter,
  u.amount
FROM quarterly_report r
CROSS JOIN LATERAL (
  VALUES
    ('Q1', r.q1),
    ('Q2', r.q2),
    ('Q3', r.q3),
    ('Q4', r.q4)
) AS u(quarter, amount)
ORDER BY r.customer_id, u.quarter;
-- Unlike SQL Server UNPIVOT, this preserves NULL values

Oracle: PIVOT and UNPIVOT

-- PIVOT
SELECT *
FROM (SELECT department, job_title, salary FROM employees)
PIVOT (
  AVG(salary) FOR job_title IN ('Manager' AS mgr, 'Engineer' AS eng, 'Analyst' AS anl)
)
ORDER BY department;

-- UNPIVOT (preserving NULLs)
SELECT department, job_title, avg_salary
FROM pivoted_employees
UNPIVOT INCLUDE NULLS (
  avg_salary FOR job_title IN (mgr AS 'Manager', eng AS 'Engineer', anl AS 'Analyst')
);

Anti-Patterns

Wrong: Using UNPIVOT when source columns contain NULLs (SQL Server)

-- BAD — silently drops rows where Q2 or Q3 is NULL
SELECT customer_id, quarter, amount
FROM quarterly_report
UNPIVOT (amount FOR quarter IN (Q1, Q2, Q3, Q4)) AS u;

Correct: Use CROSS APPLY VALUES to preserve NULLs

-- GOOD — preserves all rows including NULLs
SELECT customer_id, quarter, amount
FROM quarterly_report
CROSS APPLY (
  VALUES ('Q1', Q1), ('Q2', Q2), ('Q3', Q3), ('Q4', Q4)
) AS u(quarter, amount);

Wrong: Unsorted input to PostgreSQL crosstab()

-- BAD — missing ORDER BY causes values in wrong columns
SELECT *
FROM crosstab(
  'SELECT dept, quarter, revenue FROM sales GROUP BY dept, quarter'
) AS ct(dept TEXT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC);

Correct: Always sort by row-name column and provide category query

-- GOOD — sorted input + explicit category list
SELECT *
FROM crosstab(
  'SELECT dept, quarter, revenue FROM sales
   GROUP BY dept, quarter ORDER BY dept, quarter',
  $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$
) AS ct(dept TEXT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC);

Wrong: String concatenation in dynamic pivot (SQL injection risk)

-- BAD — direct string interpolation allows SQL injection
SET @sql = 'SELECT * FROM t PIVOT (SUM(val) FOR col IN (' + @user_input + '))';
EXEC(@sql);

Correct: Use QUOTENAME and validate inputs

-- GOOD — QUOTENAME escapes identifiers; validate against whitelist
SELECT @columns = STRING_AGG(QUOTENAME(col_value), ', ')
FROM (SELECT DISTINCT col_value FROM t
      WHERE col_value IN (SELECT allowed_value FROM whitelist)) AS safe;

SET @sql = N'SELECT * FROM t PIVOT (SUM(val) FOR col IN (' + @columns + N')) AS pvt;';
EXEC sp_executesql @sql;

Wrong: Using SUM(CASE) when values are not aggregatable

-- BAD — SUM on non-numeric status values silently returns 0 or errors
SELECT id,
  SUM(CASE WHEN attr = 'color' THEN value END) AS color
FROM attributes
GROUP BY id;

Correct: Use MAX or MIN for non-aggregated single values

-- GOOD — MAX returns the single value per group for non-numeric columns
SELECT id,
  MAX(CASE WHEN attr = 'color' THEN value END) AS color,
  MAX(CASE WHEN attr = 'size'  THEN value END) AS size
FROM attributes
GROUP BY id;

Common Pitfalls

• Missing tablefunc extension: PostgreSQL crosstab() fails with "function crosstab(unknown) does not exist". Fix: CREATE EXTENSION IF NOT EXISTS tablefunc; — requires superuser or database owner. [src1]
• Unsorted crosstab input: Values silently land in wrong columns when the source query is not sorted by the row-name column. Fix: Always add ORDER BY row_name_col, category_col to the inner query. [src1]
• NULL swallowing in UNPIVOT: SQL Server UNPIVOT silently removes rows with NULL values — data loss goes unnoticed. Fix: Use CROSS APPLY (VALUES ...) instead, or wrap columns in ISNULL(col, default). [src2]
• MySQL GROUP_CONCAT truncation: Dynamic pivot strings truncated at group_concat_max_len (default 1024 bytes). Fix: SET SESSION group_concat_max_len = 1000000; before the query. [src7]
• Oracle UNPIVOT NULL exclusion: Default is EXCLUDE NULLS. Fix: Add INCLUDE NULLS keyword. [src4]
• Aggregate function mismatch: Using SUM for single-valued pivots returns wrong results when duplicates exist; using MAX for aggregations ignores lower values. Fix: Match the aggregate to the semantic meaning — SUM for totals, MAX/MIN for single values, COUNT for frequencies. [src3]
• Column count mismatch in crosstab: The output column definition in AS ct(...) must exactly match the number of category values — extra or missing columns cause errors or silent misalignment. Fix: Count distinct category values and match the column list. [src1]
• Performance on large datasets: Conditional aggregation (CASE/FILTER) scans the entire table once; UNION ALL unpivot scans it N times (once per column). Fix: For large unpivots, use LATERAL VALUES (single scan) instead of UNION ALL. [src5]

Version History & Compatibility

When to Use / When Not to Use

Important Caveats

• Dynamic pivot queries are inherently vulnerable to SQL injection — always use parameterization (QUOTENAME on SQL Server, format('%I', ...) on PostgreSQL) and validate against a whitelist
• crosstab() with the single-parameter form (no category query) assigns values positionally — this silently produces wrong results when some row/category combinations are missing
• Oracle PIVOT XML returns XML-typed columns, not scalar values — useful for dynamic column lists but requires XML parsing downstream
• Performance of conditional aggregation degrades linearly with the number of pivot columns — at 50+ columns, consider restructuring the query or using application-layer pivoting
• UNPIVOT does not round-trip perfectly with PIVOT — aggregation during pivot merges rows, so unpivoting the result produces fewer rows than the original