PIVOT & UNPIVOT Patterns

Advanced SQL

Reshape Data with PIVOT & UNPIVOT

PIVOT transforms rows into columns for matrix-style reporting. UNPIVOT does the reverse — turning columns back into rows for normalized analysis. Together, they are the most powerful data reshaping tools in SQL.

Matrix Reporting — Convert time-series rows into quarterly/monthly columns
Dynamic PIVOT — Generate columns from data values at query time
UNPIVOT Patterns — Normalize wide tables back to row-level format
Cross-Platform — Syntax for PostgreSQL, SQL Server, BigQuery, and MySQL

PIVOT turns "how did each department perform each quarter?" from a complex query into a clean, readable report.

What Is PIVOT?

PIVOT rotates a table by converting row values into column headers, aggregating the values at the intersection.

Mathematical Definition

Given a table with columns , PIVOT produces:

where each column contains:

"PIVOT is SQL's way of creating a cross-tabulation — the same operation as a spreadsheet pivot table." — Microsoft SQL Server Documentation

PIVOT Transformation Visualized

Native PIVOT Syntax

SQL Server / PostgreSQL 14+

-- Native PIVOT syntax
SELECT *
FROM sales_data
PIVOT (
  SUM(amount)
  FOR product_category IN (
    'Electronics' AS electronics,
    'Clothing' AS clothing,
    'Food' AS food,
    'Books' AS books
  )
) AS pivot_table;

BigQuery

-- BigQuery native PIVOT
SELECT *
FROM (
  SELECT department, product_category, amount
  FROM `project.dataset.sales`
)
PIVOT (
  SUM(amount)
  FOR product_category IN (
    'Electronics' AS electronics,
    'Clothing' AS clothing,
    'Food' AS food
  )
);

ℹ️

Key Insight: The PIVOT operator requires an aggregate function — you can't use PIVOT without aggregating. The aggregate (SUM, COUNT, AVG) determines what value appears at each intersection of row and column.

Manual PIVOT with CASE

Cross-tabulation using conditional aggregation (works on all databases):

-- Quarterly revenue pivot using CASE
SELECT
  department,
  SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 1 THEN amount END) AS q1,
  SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 2 THEN amount END) AS q2,
  SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 3 THEN amount END) AS q3,
  SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 4 THEN amount END) AS q4,
  SUM(amount) AS total,
  ROUND(
    SUM(CASE WHEN EXTRACT(QUARTER FROM sale_date) = 1 THEN amount END) * 100.0 /
    NULLIF(SUM(amount), 0), 1
  ) AS q1_pct
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = 2024
GROUP BY department
ORDER BY total DESC;

PIVOT Formula

For a PIVOT operation, each output cell is computed as:

Dynamic PIVOT with Unknown Columns

When column values are unknown at query time, build the PIVOT dynamically:

-- PostgreSQL: Dynamic pivot using string interpolation
DO $$
DECLARE
  pivot_sql TEXT;
  col_list TEXT;
BEGIN
  -- Build column list dynamically from data
  SELECT STRING_AGG(
    DISTINCT '''' || product_category || ''' AS ' ||
    REPLACE(product_category, ' ', '_'),
    ', '
  )
  INTO col_list
  FROM sales_data;

  -- Construct and execute pivot query
  pivot_sql := format('
    SELECT
      department,
      %s,
      SUM(amount) AS total
    FROM sales_data
    GROUP BY department
    ORDER BY total DESC
  ', col_list);

  RAISE NOTICE '%', pivot_sql;
END $$;

⚠️

Performance Warning: Dynamic PIVOT requires dynamic SQL, which can't be prepared/cached. For large datasets, consider materializing the pivot columns first, or using a BI tool that handles pivoting at the presentation layer.

UNPIVOT Patterns

CROSS JOIN LATERAL (PostgreSQL, MySQL 8+)

-- Convert columns to rows (UNPIVOT)
SELECT
  employee_id,
  quarter,
  amount
FROM quarterly_sales
CROSS JOIN LATERAL (
  VALUES
    ('Q1', q1_amount),
    ('Q2', q2_amount),
    ('Q3', q3_amount),
    ('Q4', q4_amount)
) AS t(quarter, amount)
WHERE amount IS NOT NULL;

unnest with Arrays (PostgreSQL)

-- Using unnest for UNPIVOT
SELECT
  employee_id,
  quarter,
  amount
FROM quarterly_sales
CROSS JOIN unnest(
  ARRAY['Q1', 'Q2', 'Q3', 'Q4'],
  ARRAY[q1_amount, q2_amount, q3_amount, q4_amount]
) AS t(quarter, amount);

UNION ALL (All Databases)

-- Universal UNPIVOT using UNION ALL
SELECT employee_id, 'Q1' AS quarter, q1_amount AS amount FROM quarterly_sales
UNION ALL
SELECT employee_id, 'Q2', q2_amount FROM quarterly_sales
UNION ALL
SELECT employee_id, 'Q3', q3_amount FROM quarterly_sales
UNION ALL
SELECT employee_id, 'Q4', q4_amount FROM quarterly_sales;

Matrix-Style Reporting

-- Daily active users pivot for dashboard reporting
WITH daily_stats AS (
  SELECT
    DATE(event_time) AS event_date,
    platform,
    COUNT(DISTINCT user_id) AS dau
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY 1, 2
)
SELECT
  event_date,
  SUM(CASE WHEN platform = 'ios' THEN dau END) AS ios_dau,
  SUM(CASE WHEN platform = 'android' THEN dau END) AS android_dau,
  SUM(CASE WHEN platform = 'web' THEN dau END) AS web_dau,
  SUM(dau) AS total_dau,
  ROUND(SUM(CASE WHEN platform = 'ios' THEN dau END) * 100.0 / NULLIF(SUM(dau), 0), 1) AS ios_pct
FROM daily_stats
GROUP BY event_date
ORDER BY event_date;

Multi-Column PIVOT

Pivot multiple measures simultaneously using conditional aggregation:

-- Pivot multiple metrics in one query
SELECT
  department,
  SUM(CASE WHEN metric = 'avg_salary' THEN value END) AS avg_salary,
  SUM(CASE WHEN metric = 'min_salary' THEN value END) AS min_salary,
  SUM(CASE WHEN metric = 'max_salary' THEN value END) AS max_salary,
  SUM(CASE WHEN metric = 'headcount' THEN value END) AS headcount,
  SUM(CASE WHEN metric = 'new_hires' THEN value END) AS new_hires
FROM (
  SELECT department, 'avg_salary' AS metric, AVG(salary) AS value FROM employees GROUP BY department
  UNION ALL
  SELECT department, 'min_salary', MIN(salary) FROM employees GROUP BY department
  UNION ALL
  SELECT department, 'max_salary', MAX(salary) FROM employees GROUP BY department
  UNION ALL
  SELECT department, 'headcount', COUNT(*)::NUMERIC FROM employees GROUP BY department
  UNION ALL
  SELECT department, 'new_hires', COUNT(*)::NUMERIC FROM employees
  WHERE hire_date >= CURRENT_DATE - INTERVAL '90' DAY GROUP BY department
) sub
GROUP BY department;

PIVOT with Percentage Calculation

-- Pivot with row-wise percentage
WITH pivoted AS (
  SELECT
    department,
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending,
    COUNT(*) AS total
  FROM employees
  GROUP BY department
)
SELECT
  department,
  active, inactive, pending, total,
  ROUND(active * 100.0 / NULLIF(total, 0), 1) AS active_pct,
  ROUND(inactive * 100.0 / NULLIF(total, 0), 1) AS inactive_pct,
  ROUND(pending * 100.0 / NULLIF(total, 0), 1) AS pending_pct
FROM pivoted
ORDER BY total DESC;

Performance Comparison

PIVOT Method Performance

Method	Database Support	Readability	Flexibility	Performance
Native PIVOT	SQL Server, PG 14+	High	Low (fixed columns)	Fast
CASE-based	All databases	Medium	High	Fast
Dynamic SQL	All databases	Low	Very High	Moderate
Crosstab (PG)	PostgreSQL	Medium	Medium	Fast

For most use cases, CASE-based pivoting offers the best balance of portability, readability, and performance.

Quiz: Test Your Knowledge

Follow-Up Questions

How would you pivot data when the number of columns is unknown at query time?
What's the performance impact of using CASE-based pivoting vs native PIVOT?
How do you handle NULL values in UNPIVOT operations?
Explain how to pivot multiple measures without using UNION ALL.
What's the best approach for pivoting data in a columnar database like BigQuery?
How would you create a running total within each pivoted column?

Key Takeaways

PIVOT requires aggregation — always use SUM, COUNT, or another aggregate function
CASE-based pivoting is the most portable and readable approach across all databases
Dynamic PIVOT needs dynamic SQL — consider materializing columns or using BI tools
UNION ALL is the universal UNPIVOT method — works everywhere
NULL handling — use NULLIF(divisor, 0) to prevent division by zero in percentage calculations
Native PIVOT (SQL Server, PG 14+) is faster but less flexible than CASE-based approaches

PIVOT & UNPIVOT — Matrix Reporting, Dynamic Columns & Data Reshaping