Pivot & Crosstab Reports

Managers rarely want a long list of numbers. They want a grid: categories down the side, months across the top, and a total in every cell, just like a PivotTable in Excel. That grid is called a pivot table or crosstab, and you can build it directly in SQL.

In this lesson you will turn rows into columns with conditional aggregation, count things in a crosstab, add a totals row, turn wide data back into rows, and see why MySQL has no PIVOT keyword while SQL Server does. You need GROUP BY and CASE Expressions, so revise them first if they feel shaky.

Long data and wide reports

Databases store data in long form: one row per fact. A normal GROUP BY keeps it long, with one row for each category and month pair:

SELECT p.category, SUBSTR(o.order_date, 1, 7) AS month,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN products p ON p.product_id = oi.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.category, SUBSTR(o.order_date, 1, 7)
ORDER BY p.category, month;

category    | month   | revenue
------------+---------+--------
Accessories | 2025-01 | 6996
Accessories | 2025-02 | 2598
Accessories | 2025-03 | 4298
Accessories | 2025-04 | 2998
Accessories | 2025-05 | 5097
Audio       | 2025-02 | 8999
Audio       | 2025-03 | 2499
Audio       | 2025-05 | 11498
Books       | 2025-02 | 1350
Books       | 2025-03 | 900
Displays    | 2025-01 | 15999
Displays    | 2025-04 | 31998
Storage     | 2025-01 | 549
Storage     | 2025-04 | 8695
(14 rows)

The data is all there, but comparing Audio in February with Audio in May means hunting through the list. Months with no sales, such as Audio in January, are simply missing. A wide report fixes both problems: one row per category, one column per month, and a 0 where nothing was sold. SUBSTR(order_date, 1, 7) turns a date such as 2025-04-18 into the month 2025-04 and works in both MySQL and the browser.

Conditional aggregation: rows into columns

The trick is to write one aggregate for each column you want, and let a CASE decide which rows each aggregate may add up. This is called conditional aggregation. A CTE prepares the month and the amount of each order line, so the pivot itself stays readable:

WITH sales AS (
  SELECT p.category,
         SUBSTR(o.order_date, 1, 7) AS month,
         oi.quantity * oi.unit_price AS amount
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status <> 'cancelled'
)
SELECT category,
       SUM(CASE WHEN month = '2025-01' THEN amount ELSE 0 END) AS jan,
       SUM(CASE WHEN month = '2025-02' THEN amount ELSE 0 END) AS feb,
       SUM(CASE WHEN month = '2025-03' THEN amount ELSE 0 END) AS mar,
       SUM(CASE WHEN month = '2025-04' THEN amount ELSE 0 END) AS apr,
       SUM(CASE WHEN month = '2025-05' THEN amount ELSE 0 END) AS may,
       SUM(amount) AS total
FROM sales
GROUP BY category
ORDER BY total DESC;

category    | jan   | feb  | mar  | apr   | may   | total
------------+-------+------+------+-------+-------+------
Displays    | 15999 | 0    | 0    | 31998 | 0     | 47997
Audio       | 0     | 8999 | 2499 | 0     | 11498 | 22996
Accessories | 6996  | 2598 | 4298 | 2998  | 5097  | 21987
Storage     | 549   | 0    | 0    | 8695  | 0     | 9244
Books       | 0     | 1350 | 900  | 0     | 0     | 2250
(5 rows)

Read one cell to see how it works. For the Audio group, the feb column adds up amount for rows whose month is 2025-02, and adds 0 for every other row. Each SUM(CASE ...) looks at the same group of rows but keeps only its own month. MySQL prints the money with two decimals, such as 15999.00.

Three choices in this query matter:

• GROUP BY category decides the rows of the report.
• Each SUM(CASE WHEN month = ...) decides one column. You must type the column list yourself.
• ELSE 0 puts a 0 in empty cells. Without it, SUM of nothing is NULL, which looks like missing data.

Counting in a crosstab

A crosstab does not have to add money. Swap the amount for 1 and you count rows instead. The support team wants to see each state's orders split by status:

SELECT c.state,
       COUNT(*) AS orders,
       SUM(CASE WHEN o.status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN o.status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
       SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS pending,
       SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.state
ORDER BY orders DESC, c.state;

state         | orders | delivered | shipped | pending | cancelled
--------------+--------+-----------+---------+---------+----------
Haryana       | 5      | 3         | 1       | 1       | 0
Maharashtra   | 2      | 1         | 1       | 0       | 0
Rajasthan     | 2      | 2         | 0       | 0       | 0
Delhi         | 1      | 1         | 0       | 0       | 0
Kerala        | 1      | 1         | 0       | 0       | 0
Punjab        | 1      | 1         | 0       | 0       | 0
Tamil Nadu    | 1      | 0         | 0       | 0       | 1
Telangana     | 1      | 0         | 0       | 1       | 0
Uttar Pradesh | 1      | 1         | 0       | 0       | 0
(9 rows)

A quick check: the four status columns in each row add up to the orders column. Building such a check into a report is a good habit, because a typo in one WHEN value ('Shipped' instead of 'shipped') silently produces a column of zeros.

Adding a totals row

Reports usually end with a grand total. One portable way is to calculate the pivot once in a CTE, then UNION ALL a row that adds up each column. A sort_key column keeps the total at the bottom. This version groups the months into quarters:

WITH sales AS (
  SELECT p.category,
         SUBSTR(o.order_date, 1, 7) AS month,
         oi.quantity * oi.unit_price AS amount
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
  WHERE o.status <> 'cancelled'
),
by_category AS (
  SELECT category,
         SUM(CASE WHEN month BETWEEN '2025-01' AND '2025-03' THEN amount ELSE 0 END) AS q1,
         SUM(CASE WHEN month BETWEEN '2025-04' AND '2025-06' THEN amount ELSE 0 END) AS q2,
         SUM(amount) AS total
  FROM sales
  GROUP BY category
)
SELECT category, q1, q2, total
FROM (
  SELECT 1 AS sort_key, category, q1, q2, total FROM by_category
  UNION ALL
  SELECT 2, 'All categories', SUM(q1), SUM(q2), SUM(total) FROM by_category
) AS report
ORDER BY sort_key, total DESC;

category       | q1    | q2    | total
---------------+-------+-------+-------
Displays       | 15999 | 31998 | 47997
Audio          | 11498 | 11498 | 22996
Accessories    | 13892 | 8095  | 21987
Storage        | 549   | 8695  | 9244
Books          | 2250  | 0     | 2250
All categories | 44188 | 60286 | 104474
(6 rows)

MySQL also has a shortcut, WITH ROLLUP, which adds subtotal and grand-total rows to a GROUP BY for you. The browser's SQLite does not support it. In a rollup row, the grouped column is NULL, and the GROUPING() function (MySQL 8.0 and later) returns 1 for exactly those rows, so you can label them:

SELECT IF(GROUPING(category), 'All categories', category) AS category_name,
       COUNT(*) AS products,
       SUM(stock) AS units_in_stock
FROM products
GROUP BY category WITH ROLLUP
ORDER BY GROUPING(category), category;

category_name  | products | units_in_stock
---------------+----------+---------------
Accessories    | 6        | 312
Audio          | 2        | 65
Books          | 1        | 80
Displays       | 1        | 18
Storage        | 2        | 230
All categories | 12       | 705
(6 rows)

Unpivot: columns back into rows

Sometimes you receive data that is already wide, such as monthly sales targets typed into a spreadsheet with one column per month. To join or group it, you need it long again. Stack one SELECT per column with UNION ALL:

CREATE TABLE city_targets (
  city VARCHAR(40) PRIMARY KEY,
  jan INTEGER,
  feb INTEGER,
  mar INTEGER
);

INSERT INTO city_targets (city, jan, feb, mar) VALUES
  ('Rohtak', 20000, 25000, 30000),
  ('Jaipur', 15000, 15000, 20000);

SELECT city, '2025-01' AS month, jan AS target FROM city_targets
UNION ALL
SELECT city, '2025-02', feb FROM city_targets
UNION ALL
SELECT city, '2025-03', mar FROM city_targets
ORDER BY city, month;

city   | month   | target
-------+---------+-------
Jaipur | 2025-01 | 15000
Jaipur | 2025-02 | 15000
Jaipur | 2025-03 | 20000
Rohtak | 2025-01 | 20000
Rohtak | 2025-02 | 25000
Rohtak | 2025-03 | 30000
(6 rows)

Now each target sits in its own row with a month value in the same YYYY-MM form as the sales data, ready to be joined to actual revenue per city and month.

PIVOT in other databases, and dynamic columns

SQL Server and Oracle have special PIVOT and UNPIVOT operators. MySQL, PostgreSQL and SQLite do not, so conditional aggregation is the portable method. For comparison, here is the first pivot in SQL Server syntax:

-- SQL Server only
SELECT category, [2025-01], [2025-02], [2025-03]
FROM (
  SELECT p.category, FORMAT(o.order_date, 'yyyy-MM') AS month,
         oi.quantity * oi.unit_price AS amount
  FROM order_items oi
  JOIN orders o ON o.order_id = oi.order_id
  JOIN products p ON p.product_id = oi.product_id
) AS src
PIVOT (SUM(amount) FOR month IN ([2025-01], [2025-02], [2025-03])) AS pvt;

Even PIVOT needs the list of columns typed in, and empty cells come out as NULL rather than 0.

What if the columns are not known in advance, for example one column per status that might appear next year? That is called a dynamic pivot. In MySQL you build the SQL text with GROUP_CONCAT(), then run it as a prepared statement:

SET SESSION group_concat_max_len = 10000;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('SUM(CASE WHEN status = ''', status, ''' THEN 1 ELSE 0 END) AS `', status, '`')
         ORDER BY status)
FROM orders
INTO @cols;

SET @sql = CONCAT('SELECT customer_id, ', @cols, ' FROM orders GROUP BY customer_id');
PREPARE pivot_stmt FROM @sql;
EXECUTE pivot_stmt;
DEALLOCATE PREPARE pivot_stmt;

GROUP_CONCAT() cuts its result at 1,024 characters by default, which is why the first line raises the limit. Dynamic SQL is hard to read and to test, so many teams instead fetch the long result and let PHP, Python or a spreadsheet arrange the columns.

Common mistakes

• Forgetting ELSE 0. Empty cells become NULL, and NULL + 5 in a later calculation is NULL.
• Grouping by the column you are spreading. GROUP BY category, month puts the months back into rows. Group only by the row labels.
• Typos in WHEN values. A misspelt status gives a column of zeros without any error. Add a total column and check that the parts add up.

Key points

• A pivot (crosstab) report has one row per group and one column per value of another field.
• Build each column with SUM(CASE WHEN ... THEN value ELSE 0 END); use 1 as the value to count.
• GROUP BY sets the rows; the list of CASE columns sets the columns, typed by hand.
• Add a totals row with UNION ALL and a sort key, or with MySQL's WITH ROLLUP and GROUPING().
• UNION ALL turns wide columns back into rows (unpivot).
• MySQL has no PIVOT keyword; SQL Server and Oracle do. Dynamic pivots need generated SQL.

SELECT department,
       SUM(CASE WHEN hire_date < '2020-01-01' THEN 1 ELSE 0 END) AS before_2020,
       SUM(CASE WHEN hire_date BETWEEN '2020-01-01' AND '2022-12-31' THEN 1 ELSE 0 END) AS from_2020_to_2022,
       SUM(CASE WHEN hire_date >= '2023-01-01' THEN 1 ELSE 0 END) AS from_2023,
       COUNT(*) AS staff
FROM employees
GROUP BY department
ORDER BY department;