SQL Pivoting Data

Pivoting reshapes data from a tall, row-based layout into a wide, column-based summary — for example, turning one row per region-and-quarter into a single row per region with a separate column for each quarter’s total. Spreadsheets have a one-click Pivot Table button, but SQL has no universal equivalent: most engines (SQLite, MySQL, PostgreSQL) don’t ship a native PIVOT keyword at all. Instead, you build a pivot yourself with GROUP BY plus conditional aggregation. This lesson walks through exactly how that works, with runnable examples, the logical execution order, and the mistakes people make when they first try it.

Overview: How Pivoting Works

Relational tables are usually stored in long (or “tidy”) format: every fact is its own row, identified by one or more key columns. A sales table might have one row per region per quarter. That’s ideal for storage and for filtering, but terrible for a report where a human wants to scan one row per region and compare quarters side by side. Pivoting converts long data into wide format: one row per group, with the categories that used to live in a column now spread out as separate output columns.

SQL Server and Oracle offer a proprietary PIVOT operator, but it isn’t part of the SQL standard and doesn’t exist in SQLite, MySQL, or PostgreSQL. The portable, works-everywhere technique is conditional aggregation: for every target column, you write an aggregate function (usually SUM or COUNT) wrapped around a CASE expression that only counts a row when it belongs to that column’s category. The database engine doesn’t treat this as anything special — it just runs an ordinary GROUP BY query. For each group (bucket of rows sharing the same GROUP BY key), it evaluates every aggregate expression across that bucket’s rows. The CASE expression’s only job is to decide, row by row, whether that row’s value should contribute to a particular output column or be ignored (treated as 0 or NULL). A three-column pivot is really just three aggregate expressions evaluated over the same groups, computed in a single pass over the data.

Because the output columns must be named in the SELECT list, you need to know the category values (e.g. 'Q1', 'Q2', 'Q3', 'Q4') in advance. This is called a static pivot. If the categories are unknown or open-ended (say, one column per customer, and customers are added constantly), you either generate the SQL dynamically in application code, or do the pivot in a reporting/BI tool instead.

Syntax

The general pattern for a conditional-aggregation pivot:

SELECT group_column,
  SUM(CASE WHEN pivot_column = 'category1' THEN metric ELSE 0 END) AS category1,
  SUM(CASE WHEN pivot_column = 'category2' THEN metric ELSE 0 END) AS category2,
  SUM(CASE WHEN pivot_column = 'category3' THEN metric ELSE 0 END) AS category3
FROM table_name
GROUP BY group_column;

Part	Meaning
group_column	The column that identifies each output row (e.g. region, customer).
pivot_column	The column whose distinct values become new output columns (e.g. quarter, status).
metric	The numeric column being aggregated into each pivoted column (e.g. amount, salary).
SUM / COUNT / AVG / MAX	The aggregate applied inside each CASE; choose based on whether you want totals, counts, averages, or peaks.
ELSE 0	Makes non-matching rows contribute 0 instead of NULL, so SUM shows 0 for categories with no data.

Examples

Example 1: Pivoting quarterly sales by region

CREATE TABLE sales (id INTEGER PRIMARY KEY, region TEXT, quarter TEXT, amount REAL);
INSERT INTO sales VALUES
(1,'North','Q1',5000),(2,'North','Q2',6200),(3,'North','Q3',5800),(4,'North','Q4',7000),
(5,'South','Q1',4000),(6,'South','Q2',4500),(7,'South','Q3',4700),(8,'South','Q4',5100),
(9,'East','Q1',3000),(10,'East','Q2',3200),(11,'East','Q3',2900),(12,'East','Q4',3500);

SELECT region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS Q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS Q4
FROM sales
GROUP BY region
ORDER BY region;

Result:

region	Q1	Q2	Q3	Q4
East	3000	3200	2900	3500
North	5000	6200	5800	7000
South	4000	4500	4700	5100

Each region now occupies exactly one row, with the four quarters spread across four columns instead of four separate rows. The engine grouped the twelve source rows into three buckets (North, South, East) and, for each bucket, ran four independent conditional sums.

Example 2: Pivoting order counts by status

CREATE TABLE orders2 (order_id INTEGER PRIMARY KEY, customer_name TEXT, order_year INTEGER, status TEXT);
INSERT INTO orders2 VALUES
(1,'Alice',2023,'Completed'),(2,'Alice',2023,'Cancelled'),(3,'Alice',2024,'Completed'),
(4,'Bob',2023,'Completed'),(5,'Bob',2024,'Completed'),(6,'Bob',2024,'Cancelled'),
(7,'Carol',2024,'Completed');

SELECT customer_name,
  COUNT(CASE WHEN status = 'Completed' THEN 1 END) AS Completed,
  COUNT(CASE WHEN status = 'Cancelled' THEN 1 END) AS Cancelled
FROM orders2
GROUP BY customer_name
ORDER BY customer_name;

Result:

customer_name	Completed	Cancelled
Alice	2	1
Bob	2	1
Carol	1	0

Notice the CASE expressions here have no ELSE. That’s intentional: COUNT ignores NULL values, so a non-matching row simply produces NULL and isn’t counted — there’s no need to force it to a fake value. Carol has zero cancelled orders, and the query correctly reports 0 because COUNT of zero matching rows is 0.

Example 3: Pivoting with the FILTER clause

CREATE TABLE sales2 (id INTEGER PRIMARY KEY, region TEXT, quarter TEXT, amount REAL);
INSERT INTO sales2 VALUES
(1,'North','Q1',5000),(2,'North','Q2',6200),(3,'South','Q1',4000),(4,'South','Q2',4500);

SELECT region,
  SUM(amount) FILTER (WHERE quarter = 'Q1') AS Q1,
  SUM(amount) FILTER (WHERE quarter = 'Q2') AS Q2
FROM sales2
GROUP BY region
ORDER BY region;

Result:

region	Q1	Q2
North	5000	6200
South	4000	4500

The FILTER (WHERE ...) clause is equivalent to SUM(CASE WHEN ... THEN amount ELSE NULL END) but reads more clearly: “sum this column, but only over rows matching this condition.” SQLite and PostgreSQL support it; MySQL and SQL Server do not, so CASE WHEN remains the more portable choice when your code needs to run on multiple database engines.

How It Works Step by Step

Take Example 1’s query and walk through SQLite’s logical processing order:

• FROM — the engine reads the sales table, all 12 rows.
• WHERE — there’s no WHERE clause here, so no rows are filtered out yet (if there were one, it would run before grouping).
• GROUP BY region — the 12 rows are bucketed into three groups: North (4 rows), South (4 rows), East (4 rows).
• Aggregate evaluation — for each bucket, the engine evaluates every aggregate expression in the SELECT list. For the North bucket, it scans the 4 rows once and computes SUM(CASE WHEN quarter='Q1' ...), SUM(CASE WHEN quarter='Q2' ...), and so on, each pass checking the CASE condition against each row.
• SELECT — the computed group key and four aggregate results are projected into one output row per group.
• ORDER BY region — the three resulting rows are sorted alphabetically: East, North, South.

This is why a pivot query still needs exactly one row per group in GROUP BY — the pivoting itself happens entirely inside the aggregate expressions, not through any special pivot mechanism.

Common Mistakes

Mistake 1: Using the non-portable PIVOT keyword

Because SQL Server and Oracle have a PIVOT operator, people sometimes assume it works everywhere:

SELECT *
FROM sales
PIVOT (SUM(amount) FOR quarter IN ('Q1','Q2','Q3','Q4'));

This fails on SQLite, MySQL, and PostgreSQL with a syntax error near PIVOT, because none of them implement that operator. The fix is the conditional-aggregation pattern from Example 1, which works on every mainstream database:

SELECT region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2,
  SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS Q3,
  SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS Q4
FROM sales
GROUP BY region;

Mistake 2: Forgetting GROUP BY

Dropping the GROUP BY clause doesn’t cause an error — it silently collapses every row into one summary instead of one row per group:

CREATE TABLE sales_mistake (id INTEGER PRIMARY KEY, region TEXT, quarter TEXT, amount REAL);
INSERT INTO sales_mistake VALUES
(1,'North','Q1',5000),(2,'North','Q2',6200),(3,'South','Q1',4000),(4,'South','Q2',4500);

SELECT
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2
FROM sales_mistake;

This returns a single row, Q1 = 9000, Q2 = 10700 — North and South’s numbers silently added together, with the per-region breakdown lost. It looks like it “worked” because it ran without error, which makes this an easy bug to miss. Adding the missing GROUP BY region fixes it:

SELECT region,
  SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
  SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2
FROM sales_mistake
GROUP BY region
ORDER BY region;

Now North (Q1=5000, Q2=6200) and South (Q1=4000, Q2=4500) are reported separately, as intended. A related gotcha: using SUM(CASE WHEN ...) without ELSE 0 makes categories with zero matching rows show NULL instead of 0, which can break downstream math that expects a number.

Best Practices

• Know your pivot categories ahead of time — static pivots require naming every output column explicitly in the query.
• Use SUM(CASE WHEN ... THEN metric ELSE 0 END) for totals so empty categories show 0 instead of NULL.
• Use COUNT(CASE WHEN ... THEN 1 END) (no ELSE needed) when you’re counting matching rows — COUNT already ignores NULL.
• Prefer the FILTER (WHERE ...) clause over CASE WHEN when your database supports it (SQLite, PostgreSQL) — it’s more readable and less error-prone.
• Always double-check the GROUP BY column list matches every non-aggregated column in the SELECT list.
• Give pivoted columns clear, unambiguous aliases (e.g. Q1, not col1) so downstream reports are self-documenting.
• For categories that are unknown at query-writing time (e.g. one column per customer), generate the SQL dynamically in application code or perform the pivot in a BI/reporting tool instead of hardcoding columns.
• Test edge cases: categories with zero rows, and rows where the pivot column itself is NULL.

Practice Exercises

• Exercise 1: Using a table employees(id, name, department, salary, hire_date), write a query that returns a single summary row with three columns — the total salary paid in the 'Engineering', 'Sales', and 'Marketing' departments. Hint: use three SUM(CASE WHEN department = ... THEN salary ELSE 0 END) expressions with no GROUP BY, since you want one overall row.
• Exercise 2: Using a table orders(customer_id, country, order_year, amount), write a query that pivots total order amount by year, showing one row per country and one column per distinct order_year value present in your sample data.
• Exercise 3 (challenge): Take the wide result from Example 1 (one row per region with Q1–Q4 columns) and write a query that converts it back to long format — one row per region per quarter — using UNION ALL. This reverse operation is called unpivoting.

Summary

• Pivoting converts long (row-based) data into wide (column-based) data, typically for reporting.
• Most databases, including SQLite, MySQL, and PostgreSQL, have no native PIVOT keyword — use GROUP BY with conditional aggregation instead.
• The core pattern is SUM(CASE WHEN pivot_column = 'value' THEN metric ELSE 0 END) AS value, repeated once per target column.
• Use ELSE 0 with SUM to avoid NULL gaps; COUNT(CASE WHEN ...) doesn’t need an ELSE.
• SQLite and PostgreSQL also support the more readable FILTER (WHERE ...) clause as an alternative to CASE WHEN.
• Static pivots require knowing the category values in advance; unknown/open-ended categories need dynamic SQL or an application-layer pivot.
• Forgetting GROUP BY is a silent bug, not an error — always verify the row count matches the number of expected groups.