---
title: 'Pivot Operations'
source: 'https://academia.sh/en/courses/advanced-sql/pivot-operations'
course: 'Advanced SQL'
language: en
updated: '2026-08-23T07:00:37+00:00'
license: 'CC BY-SA 4.0'
---

# Pivot Operations

Pivoting from long form to wide form with conditional aggregation, the FILTER clause, the two phrasings of unpivoting, and the requirement that the column list be known when the query is written.

Every query written up to this point produced a result whose columns were known at the
time the query was written. In reports, though, the opposite is often wanted: values
sitting in rows should move up to a column header, each book genre should get its own
column, and the branches should stay in the rows.

This transformation is called a **pivot**. The source form is **long form**: each
measurement is one row, and a distinguishing column carries the value. The target form is
**wide form**: each value of the distinguishing column turns into a column. The
transformation is in tension with one of SQL's fundamental rules — the result's column
list has to be known when the query is compiled.

## Long Form

The starting point is the ordinary summary produced by grouping: loan count per
branch-and-genre pair.

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch TEXT);
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, genre TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice','Kadikoy'),(2,'Ben','Kadikoy'),(3,'Clara','Uskudar'),
  (4,'Derek','Uskudar'),(5,'Evan','Besiktas'),(6,'Fiona','Besiktas'),
  (7,'Grace','Kadikoy'),(8,'Hannah','Uskudar');
INSERT INTO book VALUES (1,'Lost Time','fiction'),(2,'Silent House','fiction'),
  (3,'Number Theory','science'),(4,'The Structure of the Universe','science'),(5,'Short History','history'),
  (6,'Anatolian Notes','history'),(7,'Poems','poetry'),(8,'Essays','essay'),
  (9,'Roadmap','fiction'),(10,'Epistemology','philosophy');
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

SELECT m.branch, k.genre, COUNT(*) AS count
FROM loan o JOIN member m ON m.id = o.member_id JOIN book k ON k.id = o.book_id
GROUP BY m.branch, k.genre
ORDER BY m.branch, k.genre;
SQL
```

```text
┌──────────┬────────────┬───────┐
│  branch  │   genre    │ count │
├──────────┼────────────┼───────┤
│ Besiktas │ essay      │ 1     │
│ Besiktas │ fiction    │ 2     │
│ Besiktas │ history    │ 1     │
│ Besiktas │ philosophy │ 1     │
│ Besiktas │ science    │ 1     │
│ Kadikoy  │ essay      │ 1     │
│ Kadikoy  │ fiction    │ 4     │
│ Kadikoy  │ history    │ 3     │
│ Kadikoy  │ poetry     │ 1     │
│ Kadikoy  │ science    │ 2     │
│ Uskudar  │ fiction    │ 2     │
│ Uskudar  │ history    │ 1     │
│ Uskudar  │ philosophy │ 1     │
│ Uskudar  │ poetry     │ 1     │
│ Uskudar  │ science    │ 3     │
└──────────┴────────────┴───────┘
```

This form is convenient for data processing: when a new genre is added, the query does
not change, only the row count grows. For reading, it is inconvenient. Comparing
Kadikoy's and Uskudar's fiction counts requires the eye to scan up and down through
fifteen rows, and cells that are zero — Besiktas has no poetry — do not appear at all.

## Pivoting with Conditional Aggregation

The portable way to pivot is to write one conditional aggregation expression per target
column. A `CASE` expression produces 1 or 0 depending on whether the row belongs to that
column; `SUM` adds them up.

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch TEXT);
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, genre TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice','Kadikoy'),(2,'Ben','Kadikoy'),(3,'Clara','Uskudar'),
  (4,'Derek','Uskudar'),(5,'Evan','Besiktas'),(6,'Fiona','Besiktas'),
  (7,'Grace','Kadikoy'),(8,'Hannah','Uskudar');
INSERT INTO book VALUES (1,'Lost Time','fiction'),(2,'Silent House','fiction'),
  (3,'Number Theory','science'),(4,'The Structure of the Universe','science'),(5,'Short History','history'),
  (6,'Anatolian Notes','history'),(7,'Poems','poetry'),(8,'Essays','essay'),
  (9,'Roadmap','fiction'),(10,'Epistemology','philosophy');
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

SELECT m.branch,
       SUM(CASE WHEN k.genre = 'fiction'    THEN 1 ELSE 0 END) AS fiction,
       SUM(CASE WHEN k.genre = 'science'    THEN 1 ELSE 0 END) AS science,
       SUM(CASE WHEN k.genre = 'history'    THEN 1 ELSE 0 END) AS history,
       SUM(CASE WHEN k.genre = 'poetry'     THEN 1 ELSE 0 END) AS poetry,
       SUM(CASE WHEN k.genre = 'essay'      THEN 1 ELSE 0 END) AS essay,
       SUM(CASE WHEN k.genre = 'philosophy' THEN 1 ELSE 0 END) AS philosophy,
       COUNT(*) AS total
FROM loan o JOIN member m ON m.id = o.member_id JOIN book k ON k.id = o.book_id
GROUP BY m.branch ORDER BY m.branch;
SQL
```

```text
┌──────────┬─────────┬─────────┬─────────┬────────┬───────┬────────────┬───────┐
│  branch  │ fiction │ science │ history │ poetry │ essay │ philosophy │ total │
├──────────┼─────────┼─────────┼─────────┼────────┼───────┼────────────┼───────┤
│ Besiktas │ 2       │ 1       │ 1       │ 0      │ 1     │ 1          │ 6     │
│ Kadikoy  │ 4       │ 2       │ 3       │ 1      │ 1     │ 0          │ 11    │
│ Uskudar  │ 2       │ 3       │ 1       │ 1      │ 0     │ 1          │ 8     │
└──────────┴─────────┴─────────┴─────────┴────────┴───────┴────────────┴───────┘
```

Fifteen rows shrank to three, and empty cells showed up as zero. This is information the
long form could not give: the difference between Besiktas **having no** poetry loans and
poetry being **unknown** at Besiktas is now readable.

If the `ELSE 0` part is omitted, non-matching rows produce a null value. Since `SUM`
skips null values, the result does not change in most cases; but if **all** of a group's
rows fail to match, the column stays null instead of zero. In reports where zero is
meaningful, either `ELSE 0` has to be written, or the result has to be wrapped in
`COALESCE`.

This phrasing's cost is a single scan: every row is read once, and the six expressions
are all evaluated over the same row. Producing the same result by combining six separate
queries would mean scanning the table six times.

## The FILTER Clause

Standard SQL defines a separate phrasing for conditional aggregation: the
`FILTER (WHERE …)` clause following an aggregate function makes that function see only
the rows that satisfy the condition. Its purpose is the same as `CASE`'s, and it reads
better — the condition sits right next to the thing being counted:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE member(id INTEGER PRIMARY KEY, name TEXT, branch TEXT);
CREATE TABLE book(id INTEGER PRIMARY KEY, title TEXT, genre TEXT);
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT, returned TEXT);
INSERT INTO member VALUES (1,'Alice','Kadikoy'),(2,'Ben','Kadikoy'),(3,'Clara','Uskudar'),
  (4,'Derek','Uskudar'),(5,'Evan','Besiktas'),(6,'Fiona','Besiktas'),
  (7,'Grace','Kadikoy'),(8,'Hannah','Uskudar');
INSERT INTO book VALUES (1,'Lost Time','fiction'),(2,'Silent House','fiction'),
  (3,'Number Theory','science'),(4,'The Structure of the Universe','science'),(5,'Short History','history'),
  (6,'Anatolian Notes','history'),(7,'Poems','poetry'),(8,'Essays','essay'),
  (9,'Roadmap','fiction'),(10,'Epistemology','philosophy');
INSERT INTO loan VALUES
  (1,1,1,'2024-03-01','2024-03-15'),(2,3,1,'2024-03-01','2024-03-20'),
  (3,5,1,'2024-03-04','2024-03-18'),(4,7,1,'2024-03-11',NULL),
  (5,9,1,'2024-03-18','2024-03-29'),(6,2,2,'2024-03-01','2024-03-12'),
  (7,4,2,'2024-03-06','2024-03-25'),(8,6,2,'2024-03-11','2024-03-19'),
  (9,8,2,'2024-03-21',NULL),(10,1,3,'2024-03-04','2024-03-10'),
  (11,3,3,'2024-03-06','2024-03-27'),(12,10,3,'2024-03-13','2024-03-22'),
  (13,2,3,'2024-03-25',NULL),(14,5,4,'2024-03-04','2024-03-09'),
  (15,7,4,'2024-03-13','2024-03-26'),(16,4,4,'2024-03-20',NULL),
  (17,6,5,'2024-03-06','2024-03-14'),(18,9,5,'2024-03-11','2024-03-23'),
  (19,1,5,'2024-03-25','2024-03-28'),(20,8,6,'2024-03-04','2024-03-17'),
  (21,10,6,'2024-03-13','2024-03-21'),(22,3,6,'2024-03-20',NULL),
  (23,2,7,'2024-03-11','2024-03-16'),(24,5,7,'2024-03-18','2024-03-24'),
  (25,4,8,'2024-03-13','2024-03-24');

SELECT m.branch,
       COUNT(*) FILTER (WHERE k.genre = 'fiction') AS fiction,
       COUNT(*) FILTER (WHERE k.genre = 'science') AS science,
       COUNT(*) FILTER (WHERE k.genre = 'history') AS history,
       COUNT(*) FILTER (WHERE o.returned IS NULL)  AS not_returned,
       COUNT(*) AS total
FROM loan o JOIN member m ON m.id = o.member_id JOIN book k ON k.id = o.book_id
GROUP BY m.branch ORDER BY m.branch;
SQL
```

```text
┌──────────┬─────────┬─────────┬─────────┬──────────────┬───────┐
│  branch  │ fiction │ science │ history │ not_returned │ total │
├──────────┼─────────┼─────────┼─────────┼──────────────┼───────┤
│ Besiktas │ 2       │ 1       │ 1       │ 1            │ 6     │
│ Kadikoy  │ 4       │ 2       │ 3       │ 2            │ 11    │
│ Uskudar  │ 2       │ 3       │ 1       │ 2            │ 8     │
└──────────┴─────────┴─────────┴─────────┴──────────────┴───────┘
```

The fiction, science, and history columns are the same as the previous result. The fourth
column shows that pivoting is not limited to a single distinguishing column:
`not_returned` is an independent condition unrelated to genre, and it was computed in the
same scan. Bringing different criteria together side by side in the same row is
conditional aggregation's real benefit, independent of pivoting.

The `FILTER` clause is standard but is not present in every engine; where it is not
supported, the `CASE` phrasing always works.

## The Reverse Direction: Column to Row

The reverse operation converts a wide-form table into long form. The need arises when a
wide summary coming from an external source is loaded into a normalized table, or when a
query is wanted that does not break as the genre count changes.

The portable phrasing is to turn the column names into a list of values and cross join
that with the source table:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE summary(branch TEXT PRIMARY KEY, fiction INT, science INT, history INT);
INSERT INTO summary VALUES ('Besiktas',2,1,1),('Kadikoy',4,2,3),('Uskudar',2,3,1);

WITH genres(genre) AS (VALUES ('fiction'),('science'),('history'))
SELECT s.branch, g.genre,
       CASE g.genre WHEN 'fiction' THEN s.fiction
                    WHEN 'science' THEN s.science
                    WHEN 'history' THEN s.history END AS count
FROM summary s CROSS JOIN genres g
ORDER BY s.branch, g.genre;
SQL
```

```text
┌──────────┬─────────┬───────┐
│  branch  │  genre  │ count │
├──────────┼─────────┼───────┤
│ Besiktas │ fiction │ 2     │
│ Besiktas │ history │ 1     │
│ Besiktas │ science │ 1     │
│ Kadikoy  │ fiction │ 4     │
│ Kadikoy  │ history │ 3     │
│ Kadikoy  │ science │ 2     │
│ Uskudar  │ fiction │ 2     │
│ Uskudar  │ history │ 1     │
│ Uskudar  │ science │ 3     │
└──────────┴─────────┴───────┘
```

The cross join matched three branches with three genres and produced nine rows; `CASE`
picked the correct column on every row. The source table is read **once**.

The same result can also be produced by writing one query per column and combining them
with `UNION ALL`:

```bash
sqlite3 -box -header <<'SQL'
CREATE TABLE summary(branch TEXT PRIMARY KEY, fiction INT, science INT, history INT);
INSERT INTO summary VALUES ('Besiktas',2,1,1),('Kadikoy',4,2,3),('Uskudar',2,3,1);

SELECT branch, 'fiction' AS genre, fiction AS count FROM summary
UNION ALL SELECT branch, 'science', science FROM summary
UNION ALL SELECT branch, 'history', history FROM summary
ORDER BY branch, genre;
SQL
```

```text
┌──────────┬─────────┬───────┐
│  branch  │  genre  │ count │
├──────────┼─────────┼───────┤
│ Besiktas │ fiction │ 2     │
│ Besiktas │ history │ 1     │
│ Besiktas │ science │ 1     │
│ Kadikoy  │ fiction │ 4     │
│ Kadikoy  │ history │ 3     │
│ Kadikoy  │ science │ 2     │
│ Uskudar  │ fiction │ 2     │
│ Uskudar  │ history │ 1     │
│ Uskudar  │ science │ 3     │
└──────────┴─────────┴───────┘
```

The results are the same; readability and cost differ. The union-based phrasing scans
the table once per column: three columns, three scans. For small summary tables this does
not matter; for a large source, the cross-join phrasing is preferred.

## Why the Column List Is Fixed

In both directions, the genre names were written into the query's text by hand. This is
not a shortcoming but a consequence of the relational model: a query's result is a
relation, and a relation's header — its column names and types — is known before the
query runs. Producing columns by looking at the data would break this definition.

The practical consequence is this: when a new book genre is added, the wide-form query
does not gain a column on its own; the query has to be updated. Anyone who wants to
derive the column list from the data has to assemble the query text on the application
side and send it to the engine. This path is open, but it carries two costs: queries
produced by string concatenation open an injection surface, and because each distinct
column list produces a different query text, it does not benefit from the plan cache.
These two subjects are taken up in the Dynamic SQL Risks lesson in the course's Query
Performance topic.

Some engines offer a `PIVOT` keyword or something similar. These phrasings are not part
of standard SQL, and they still want the column list at the time the query is written;
what they do is shorten conditional aggregation.

## Summary

- Long form is convenient for data processing, wide form for reading; a pivot is the
  transformation between these two forms.
- The portable pivot phrasing is one conditional aggregation expression per target
  column, completed in a single scan; if `ELSE 0` is not written, a group that matches
  nothing at all leaves its column null.
- The `FILTER (WHERE …)` clause writes the same job more readably and allows independent
  criteria to be gathered into the same row.
- The reverse pivot is written by turning column names into a value list and cross
  joining, in a single scan, or with `UNION ALL`, at one scan per column.
- The result columns have to be known when the query is written; deriving the column list
  from the data requires assembling the query text on the application side.

## Next Step

Every query written throughout this topic was a single statement and answered a single
question. Data-modifying work, though, often does not fit into one statement: lending a
book requires both opening a loan record and updating the book's status together. If one
of the two happens and the other does not, the data is left inconsistent. The next topic
takes up the operations that turn several statements into a single indivisible unit; its
first lesson begins with starting, committing, and rolling back a transaction.