Skip to content
academia.sh
tr
Log in

Lesson 07 / 20

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.

Contents

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.

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

┌──────────┬────────────┬───────┐
│  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.

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

┌──────────┬─────────┬─────────┬─────────┬────────┬───────┬────────────┬───────┐
│  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:

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

┌──────────┬─────────┬─────────┬─────────┬──────────────┬───────┐
│  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:

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

┌──────────┬─────────┬───────┐
│  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:

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

┌──────────┬─────────┬───────┐
│  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.

To keep your progress and take notes, Log in

My notes

Log in to take notes.

Start typing to search.

↑↓ ↵ Esc navigate · open · close