Pivot Table in MySQL

What is a pivot

A pivot table turns rows into columns to make a cross-tab summary — like sales totals with each month as its own column. MySQL has no PIVOT keyword, but you can build one easily using a CASE inside SUM. This is called conditional aggregation.

The starting data

-- sales table (rows)
+--------+-------+--------+
| region | month | amount |
+--------+-------+--------+
| North  | Jan   |   100  |
| North  | Feb   |   150  |
| South  | Jan   |   200  |
+--------+-------+--------+

We want one row per region, with Jan and Feb as columns showing the totals. That is a pivot.

CASE inside SUM

The trick is a CASE that returns the amount only for the matching month, else 0:

SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan

For each region, this adds up only the January amounts. Repeat one such expression per column you want.

The pivot query

SELECT region,
    SUM(CASE WHEN month = 'Jan' THEN amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN month = 'Feb' THEN amount ELSE 0 END) AS Feb
FROM sales
GROUP BY region;

GROUP BY region makes one row per region, and each CASE-SUM fills the right month column. The rows have become columns.

Pivoting with COUNT

SELECT region,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid,
    SUM(CASE WHEN status = 'due' THEN 1 ELSE 0 END) AS due
FROM invoices
GROUP BY region;

To count instead of total, put 1 in the THEN. This counts how many rows fall into each category — a count-based cross-tab, like paid vs due invoices per region.

Notes and limits

• You list columns manually. Each output column needs its own CASE.
• Fixed categories work best. Great for months or statuses you know in advance.
• Dynamic columns need generated SQL. For unknown values, build the query in code.

Common mistakes

• Forgetting GROUP BY. Without it, the pivot does not collapse to one row per category.
• Using ELSE NULL with SUM unintentionally. Use ELSE 0 for clean totals.
• Expecting a PIVOT keyword. MySQL has none; use CASE.
• Mismatched column labels. Name each pivot column with AS.

Summary

• A pivot turns rows into columns for a cross-tab summary.
• MySQL has no PIVOT; use SUM(CASE WHEN ... THEN ... ELSE 0 END).
• GROUP BY the row category to get one row each.
• Use 1 in the CASE to pivot with counts.
• You must list each output column, so it suits fixed categories.

Next, learn grouping in GROUP BY and HAVING, or conditional logic in the CASE expression.