SQL PIVOT and UNPIVOT Patterns: Rows to Columns and Back

What Is Pivoting?

Pivoting transforms rows into columns. Given a table of sales data with one row per product per quarter, pivoting produces one row per product with separate columns for each quarter.

Before (normalized rows):

product	quarter	revenue
Widget	Q1	1000
Widget	Q2	1500
Gadget	Q1	800
Gadget	Q2	1200

After (pivoted columns):

product	Q1	Q2
Widget	1000	1500
Gadget	800	1200

Method 1: CASE + GROUP BY (Works Everywhere)

The most portable approach uses conditional aggregation:

SELECT product,
       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 quarterly_sales
GROUP BY product
ORDER BY product;

This works in every SQL database. The CASE inside SUM returns NULL for non-matching rows, and SUM ignores NULLs.

Using FILTER (PostgreSQL)

PostgreSQL offers a cleaner syntax with FILTER:

SELECT product,
       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
FROM quarterly_sales
GROUP BY product;

Multiple Aggregates

You can pivot multiple measures at once:

SELECT product,
       SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1_revenue,
       COUNT(CASE WHEN quarter = 'Q1' THEN 1 END) AS q1_orders,
       SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2_revenue,
       COUNT(CASE WHEN quarter = 'Q2' THEN 1 END) AS q2_orders
FROM quarterly_sales
GROUP BY product;

Method 2: PostgreSQL CROSSTAB

PostgreSQL provides tablefunc with the crosstab function. First, enable the extension:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Then use crosstab:

SELECT *
FROM crosstab(
  'SELECT product, quarter, revenue
   FROM quarterly_sales
   ORDER BY product, quarter',
  'SELECT DISTINCT quarter FROM quarterly_sales ORDER BY quarter'
) AS ct(product TEXT, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC);

The first argument is the source query (must return exactly 3 columns: row identifier, category, value). The second argument provides the list of categories that become columns.

When to Use CROSSTAB

CROSSTAB is useful when the category values are dynamic but known at query time. However, the output columns must still be declared in the AS clause, so truly dynamic pivoting requires dynamic SQL.

Method 3: SQL Server PIVOT

SQL Server has a built-in PIVOT operator:

SELECT product, Q1, Q2, Q3, Q4
FROM (
  SELECT product, quarter, revenue
  FROM quarterly_sales
) AS src
PIVOT (
  SUM(revenue)
  FOR quarter IN (Q1, Q2, Q3, Q4)
) AS pvt;

The FOR ... IN clause lists the values to become columns. These must be known at query time.

Unpivoting: Columns to Rows

Unpivoting is the reverse operation. Given a table with separate columns for each quarter, produce one row per quarter.

Before (wide):

product	q1	q2	q3	q4
Widget	1000	1500	2000	1800

After (long):

product	quarter	revenue
Widget	q1	1000
Widget	q2	1500
Widget	q3	2000
Widget	q4	1800

UNION ALL (Works Everywhere)

SELECT product, 'q1' AS quarter, q1 AS revenue FROM wide_sales
UNION ALL
SELECT product, 'q2', q2 FROM wide_sales
UNION ALL
SELECT product, 'q3', q3 FROM wide_sales
UNION ALL
SELECT product, 'q4', q4 FROM wide_sales
ORDER BY product, quarter;

PostgreSQL VALUES + LATERAL

A cleaner approach in PostgreSQL:

SELECT w.product, u.quarter, u.revenue
FROM wide_sales w
CROSS JOIN LATERAL (
  VALUES ('q1', w.q1), ('q2', w.q2), ('q3', w.q3), ('q4', w.q4)
) AS u(quarter, revenue);

SQL Server UNPIVOT

SELECT product, quarter, revenue
FROM wide_sales
UNPIVOT (
  revenue FOR quarter IN (q1, q2, q3, q4)
) AS unpvt;

Dynamic Pivoting

When the category values are not known ahead of time, you need dynamic SQL.

PostgreSQL Dynamic Pivot

DO $$
DECLARE
  cols TEXT;
  query TEXT;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter = %L THEN revenue END) AS %I', quarter, quarter),
    ', '
  )
  INTO cols
  FROM (SELECT DISTINCT quarter FROM quarterly_sales ORDER BY quarter) sub;

  query := format('SELECT product, %s FROM quarterly_sales GROUP BY product ORDER BY product', cols);
  RAISE NOTICE '%', query;
  -- Execute or return via a function
END $$;

For a reusable solution, wrap this in a function that returns SETOF RECORD or use a temporary table.

SQL Server Dynamic Pivot

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

SELECT @cols = STRING_AGG(QUOTENAME(quarter), ', ')
FROM (SELECT DISTINCT quarter FROM quarterly_sales) AS q;

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

EXEC sp_executesql @sql;

Real-World Example: Attendance Report

-- Source data: one row per student per day
CREATE TABLE attendance (
  student_name TEXT,
  day_of_week  TEXT,
  status       TEXT  -- 'present' or 'absent'
);

-- Pivot into a weekly attendance grid
SELECT student_name,
       MAX(CASE WHEN day_of_week = 'Monday'    THEN status END) AS monday,
       MAX(CASE WHEN day_of_week = 'Tuesday'   THEN status END) AS tuesday,
       MAX(CASE WHEN day_of_week = 'Wednesday' THEN status END) AS wednesday,
       MAX(CASE WHEN day_of_week = 'Thursday'  THEN status END) AS thursday,
       MAX(CASE WHEN day_of_week = 'Friday'    THEN status END) AS friday
FROM attendance
GROUP BY student_name
ORDER BY student_name;

Real-World Example: Survey Results

-- Source: one row per response per question
SELECT respondent_id,
       AVG(CASE WHEN question = 'satisfaction' THEN score END) AS satisfaction,
       AVG(CASE WHEN question = 'likelihood_to_recommend' THEN score END) AS nps,
       AVG(CASE WHEN question = 'ease_of_use' THEN score END) AS ease_of_use
FROM survey_responses
GROUP BY respondent_id;

Performance Tips

1. Filter before pivoting. Apply WHERE clauses in a subquery to reduce the number of rows the CASE expressions evaluate.
2. Index the category column if the source table is large.
3. Materialize frequent pivots. If a pivot query runs on every page load, create a materialized view.

CREATE MATERIALIZED VIEW mv_quarterly_pivot AS
SELECT product,
       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 quarterly_sales
GROUP BY product;

Summary

Pivoting with CASE + GROUP BY is the most portable pattern and works in every SQL database. PostgreSQL’s crosstab and SQL Server’s PIVOT offer syntactic convenience. For unpivoting, use UNION ALL universally or LATERAL VALUES in PostgreSQL. When category values are dynamic, build the query string with dynamic SQL. Always filter early and consider materializing frequently-used pivots.