SQL PIVOT and UNPIVOT Tutorial

What and Why

Pivoting turns rows into columns: one row per group, with one column per category. Unpivoting does the opposite: it turns wide tables back into tall ones. Both come up constantly in reporting, dashboards, and data preparation for charts.

Most relational engines do not have a single portable syntax for either operation. Knowing the patterns lets you handle pivots in any dialect without reaching for a spreadsheet.

Mental Model

A pivot is a GROUP BY plus an aggregate per output column. An unpivot is the inverse: a single row with N columns becomes N rows with two columns (key, value). Once you see them as group-and-aggregate vs split-and-stack, the syntax stops mattering.

Hands-on Example

Suppose sales(region, quarter, revenue):

Pivoted by quarter (portable CASE form, works everywhere):

SELECT
  region,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue END) AS q4
FROM sales
GROUP BY region;

In Postgres with the tablefunc extension:

SELECT * FROM crosstab(
  'SELECT region, quarter, revenue FROM sales ORDER BY 1,2',
  $$VALUES ('Q1'),('Q2'),('Q3'),('Q4')$$
) AS ct(region text, q1 int, q2 int, q3 int, q4 int);

In SQL Server:

SELECT region, [Q1], [Q2], [Q3], [Q4]
FROM sales
PIVOT (SUM(revenue) FOR quarter IN ([Q1],[Q2],[Q3],[Q4])) p;

To unpivot the wide result back to tall, use UNION ALL or Postgres’ LATERAL:

SELECT region, quarter, revenue
FROM wide_sales
CROSS JOIN LATERAL (VALUES
  ('Q1', q1), ('Q2', q2), ('Q3', q3), ('Q4', q4)
) AS u(quarter, revenue);

Tall (rows)            Wide (pivot)
region quarter rev     region q1  q2  q3  q4
US     Q1      100     US     100 150  -   -
US     Q2      150     EU      80 120  -   -
EU     Q1       80
EU     Q2      120
                     UNPIVOT
                     <-----------
                     PIVOT
                     ----------->

The pivot collapses many rows per region into one. The unpivot expands one wide row back into many tall ones.

Common Pitfalls

The biggest one is dynamic pivots. SQL is statically typed: the result columns must be known at parse time. If quarters or categories are not fixed, you cannot express the pivot in a single static query. You either generate the SQL dynamically (string-build server-side) or pivot in the application after fetching the tall result.

Another mistake is forgetting that CASE WHEN ... THEN x END without an ELSE returns NULL, and SUM(NULL) is NULL if no rows match. Wrap with COALESCE(SUM(...), 0) if you need zeros.

People also pivot before aggregating instead of letting the aggregate do both jobs. Always combine: SUM(CASE WHEN ... THEN revenue END) rather than pivoting a subquery and then summing.

For unpivot with UNION ALL, double-check that every branch projects the same column types, or you will get implicit casts that break indexes downstream.

Practical Tips

Use the portable CASE pattern when in doubt. It works in every database, is easy to read, and is easy to extend by adding another column. Reach for vendor-specific PIVOT, crosstab, or UNPIVOT only when the column list is large enough that the boilerplate hurts.

Push pivots as late as possible. If your frontend or BI tool can pivot, let it: querying the tall form keeps SQL simple and indexes effective. Pivot in SQL only when the consumer truly needs the wide shape (CSV exports, fixed-column reports).

For unpivots, LATERAL with VALUES in Postgres is the cleanest pattern - no repeated table scans and the column list is explicit.

When pivoting time series, always include a calendar table or generate_series to fill missing periods with zeros. Otherwise quarters with no sales silently disappear from the result.

Wrap-up

Pivot and unpivot are reshaping operations: rows to columns and back. The CASE-aggregate pattern is portable and clear; vendor-specific syntax saves typing on large pivots. Watch out for dynamic columns, missing categories, and pivoting too early in the pipeline. Reshape only when the output truly needs it, and your queries will stay maintainable.