Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 125 ยท Aggregation

Pivoting Rows into Columns

Pivot SQL results into a cross-tab with conditional aggregation (CASE and FILTER), unpivot columns back into rows with UNION ALL, and compare PIVOT in SQL Server and Oracle.

GROUP BY returns data in a long shape: one row for every combination, like (quarter, status, orders). People reading a report usually prefer a wide shape, a grid like a spreadsheet: one row per quarter and one column per status. Turning rows into columns like that is called pivoting, and the result is often called a cross-tab.

Standard SQL has no PIVOT keyword, but it doesn't need one. A technique called conditional aggregation pivots data in every database, including SQLite. In this lesson you'll learn it with CASE and with the FILTER clause, then go the other way (unpivoting), and finally see the PIVOT syntax some databases add on top.

Long data vs wide data

Here is the long version: orders counted per quarter and status. It's correct, but hard to compare at a glance:

SQL The long shape ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Sixteen rows, four per quarter. The wide version will have four rows (one per quarter) and a column for each of the four statuses: the same sixteen numbers, laid out as a grid.

Conditional aggregation with CASE

The trick is to group only by the row label (the quarter) and build each column with its own aggregate that only counts matching rows:

SQL Pivot with CASE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Here's what happens inside each quarter's group. The CASE expression is evaluated for every order in the group: it yields 1 if the order has the column's status and 0 otherwise. SUM adds those ones up, so each column counts only its own status. The total column is an ordinary COUNT(*), and each row's four status columns add up to it.

Compare a few cells with the long result above: Q1 has 5 cancelled orders and 16 delivered ones in both shapes.

Variations you'll see

The same idea can be written in a few ways, and you should recognize all of them:

SQL
-- 1. SUM of 1/0 (shown above): portable, gives 0 for "none"
SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)

-- 2. COUNT of 1/NULL: COUNT ignores NULLs, also gives 0 for "none"
COUNT(CASE WHEN status = 'cancelled' THEN 1 END)

-- 3. SUM of a boolean: SQLite, PostgreSQL (with ::int) and MySQL only
SUM(status = 'cancelled')

To pivot an amount instead of a count, put the amount in the THEN branch: SUM(CASE WHEN status = 'delivered' THEN shipping ELSE 0 END).

Watch the ELSE. SUM(CASE WHEN โ€ฆ THEN amount END) without ELSE 0 returns NULL, not 0, for a group that has no matching rows, because the sum of nothing is NULL. Either write ELSE 0 or wrap the aggregate in COALESCE(โ€ฆ, 0). For AVG, MIN and MAX you want the missing ELSE (a NULL), because a zero would distort the result.

The FILTER clause

The SQL standard has a cleaner way to say "aggregate only some rows": the FILTER clause. It attaches a WHERE condition to a single aggregate. SQLite has supported it since version 3.30, and PostgreSQL since 9.4. Here it pivots revenue by category and quarter:

SQL Pivot with FILTER ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SUM(x) FILTER (WHERE condition) means "sum x over the rows of the group where the condition holds". It reads more naturally than a CASE, and it works with any aggregate, including COUNT(*) FILTER (WHERE โ€ฆ). Like a CASE without ELSE, a filtered SUM returns NULL when no row matches. The joins here follow the pattern from Grouping in Practice; you'll study them properly in Why Joins?.

FILTER isn't available everywhere, though:

Database FILTER (WHERE โ€ฆ) Use instead
SQLite 3.30+ Yes
PostgreSQL 9.4+ Yes
MySQL / MariaDB No SUM(CASE โ€ฆ) or SUM(condition)
SQL Server No SUM(CASE โ€ฆ)
Oracle No SUM(CASE โ€ฆ)

When portability matters, CASE is the safe choice.

Pivoting a value: a report card

Pivots don't have to count. Sometimes each cell holds a single value, like a student's average grade in each term. Group by the student and use AVG with a condition on the term:

SQL Average grade per term ยท database: school

Press Run (or Ctrl + Enter) to see the result here.

Here the CASE deliberately has no ELSE. Rows from other terms become NULL, and AVG ignores them; an ELSE 0 would drag every average down. Some enrollments in Autumn 2025 have no grade yet (grade is NULL), and AVG skips those too.

When each cell is guaranteed to hold at most one row (say, one grade per student per course), the conventional spelling is MAX(CASE WHEN โ€ฆ THEN value END). MAX simply picks the one non-null value, and it also works for text.

Unpivoting: columns back into rows

Data sometimes arrives already wide: a spreadsheet export with one column per quarter. To group, filter or join it, you usually want it long again. The portable way to unpivot is one SELECT per column, stacked with UNION ALL (set operations):

SQL Unpivot with UNION ALL

Press Run (or Ctrl + Enter) to see the result here.

Two regions ร— four quarter columns become eight rows. South's Q3 target is NULL, and it survives as a row with a NULL target. Add WHERE target IS NOT NULL around the union (or to each part) if you want to drop empty cells, which is what SQL Server's UNPIVOT does by default.

Unpivoting with a cross join

Repeating the table scan once per column gets clumsy with many columns. An alternative pairs every row with a small list of column labels and picks the matching value with CASE:

SQL Unpivot with a label list

Press Run (or Ctrl + Enter) to see the result here.

(VALUES ('Q1'), โ€ฆ) is a tiny inline table; SQLite names its column column1. CROSS JOIN combines each region with each label (see CROSS JOIN). The result is the same eight rows. PostgreSQL can name the columns directly: (VALUES ('Q1'), โ€ฆ) AS q(quarter).

PIVOT and UNPIVOT in other databases

SQL Server and Oracle add PIVOT and UNPIVOT operators. They are shorthand for conditional aggregation; you still have to list every output column yourself:

SQL
-- SQL Server
SELECT quarter, [pending], [shipped], [delivered], [cancelled]
FROM (
  SELECT quarter, status, id FROM orders_with_quarter
) AS src
PIVOT (
  COUNT(id) FOR status IN ([pending], [shipped], [delivered], [cancelled])
) AS p;
SQL
-- Oracle
SELECT *
FROM (SELECT quarter, status FROM orders_with_quarter)
PIVOT (
  COUNT(*) FOR status IN ('pending' AS pending, 'shipped' AS shipped,
                          'delivered' AS delivered, 'cancelled' AS cancelled)
);
SQL
-- SQL Server: wide targets back to rows (NULL cells are dropped)
SELECT region, quarter, target
FROM sales_targets
UNPIVOT (target FOR quarter IN (q1, q2, q3, q4)) AS u;

PostgreSQL has no PIVOT; its optional tablefunc extension provides a crosstab() function, but most people use FILTER instead. MySQL and MariaDB have neither, so conditional aggregation is the standard tool there.

Database Pivot Unpivot
SQLite CASE / FILTER UNION ALL or cross join
PostgreSQL FILTER (or crosstab()) UNION ALL, cross join, LATERAL (VALUES โ€ฆ)
MySQL / MariaDB CASE / SUM(condition) UNION ALL or cross join
SQL Server PIVOT or CASE UNPIVOT or CROSS APPLY (VALUES โ€ฆ)
Oracle PIVOT or CASE UNPIVOT

When the columns aren't known in advance

Every technique above has the same limit: the output columns are fixed when you write the query. SQL needs to know a result's columns before it reads any data. If a new status such as 'returned' appears next month, your pivot won't grow a column for it on its own.

You have three options:

  • List the expected values and add an "other" column: SUM(CASE WHEN status NOT IN (โ€ฆ) THEN 1 ELSE 0 END).
  • Generate the SQL in application code (or with dynamic SQL in a stored procedure) after first querying the distinct values.
  • Return one JSON column instead of many columns, and let the application spread it out. SQLite's JSON functions make that easy:
SQL A dynamic pivot as JSON ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The inner query is the long shape from the start of the lesson. The outer query folds each quarter's rows into one JSON object such as {"cancelled":5,"delivered":16,โ€ฆ}. A new status would simply appear as a new key.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In a conditional-aggregation pivot, what goes in GROUP BY?

    You group by what becomes a row (for example the quarter). Each output column is a separate aggregate with a condition, such as SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END).
  2. 2. A group has no rows with status 'cancelled'. What does SUM(CASE WHEN status = 'cancelled' THEN 1 END) return for it?

    Without an ELSE, every row yields NULL, and the SUM of only NULLs is NULL. Add ELSE 0 or use COALESCE to get 0.
  3. 3. You pivot average grades per term. Why should the CASE have no ELSE 0?

    AVG ignores NULLs, so rows from other terms must become NULL. An ELSE 0 turns them into real zeros that count toward the average.
  4. 4. Which databases support COUNT(*) FILTER (WHERE status = 'shipped')?

    FILTER is standard SQL, implemented by SQLite (3.30+) and PostgreSQL (9.4+). MySQL, SQL Server and Oracle use CASE instead.
  5. 5. How do you turn the columns q1, q2, q3 and q4 into rows in SQLite?

    SQLite has no UNPIVOT. Each column becomes its own SELECT with a label, and UNION ALL stacks them.

Summary

  • Pivoting turns rows into columns (long โ†’ wide); unpivoting does the reverse.
  • Conditional aggregation pivots in every database: group by the row label and give each column its own SUM(CASE WHEN โ€ฆ THEN 1 ELSE 0 END).
  • agg(โ€ฆ) FILTER (WHERE โ€ฆ) is the standard, more readable form, supported by SQLite and PostgreSQL.
  • Use ELSE 0 for counts and sums, but leave it out for AVG, MIN and MAX; MAX(CASE โ€ฆ) picks a single value per cell.
  • Unpivot with UNION ALL or a cross join with a list of labels.
  • SQL Server and Oracle offer PIVOT/UNPIVOT, but columns must still be listed; for truly dynamic columns, generate SQL or return JSON.

Next: Why Joins?, the start of the Joins chapter, where you'll learn how to combine tables properly.