Pivoting and Crosstabs

MySQL has no PIVOT operator; rows become columns through conditional aggregation, for a column list you know in advance.

SyntaxSELECT key_col, SUM(CASE WHEN bucket_col = 'x' THEN value_col END) AS x, ... FROM t GROUP BY key_col

MySQL 8.4 has no PIVOT operator. Turning rows into columns is done with conditional aggregation: group by whatever should stay a row, then write one aggregate per column, each looking only at the rows belonging in that column.

The pattern

A CASE with no ELSE yields NULL, and SUM, AVG and COUNT skip NULLs — so an empty cell comes back as NULL. Add ELSE 0 only if you want a zero there, and be aware it changes what AVG computes.

Because a comparison evaluates to 1 or 0 in MySQL, these two are the same except when the condition is NULL:

SUM(status = 'paid')
SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)

With a NULL status the comparison is NULL and SUM ignores it; the CASE counts it as 0. MySQL does not support the standard FILTER clause, so CASE inside the aggregate is the equivalent.

Where it stops

The column list is fixed when you write the query. A pivot whose columns come from the data needs generated SQL — GROUP_CONCAT the CASE expressions, then PREPARE and EXECUTE. It works, but the result set changes shape underneath your application, the generated text is an injection surface if category names are user-supplied, and GROUP_CONCAT truncates at group_concat_max_len.

Often the honest answer is to return the long form — one row per cell — and pivot in the application or the reporting tool, which can size the grid at render time.

Example

Example · sql
-- Revenue per product, one column per quarter of 2025.
SELECT
    p.name,
    -- No ELSE: a quarter with no sales comes back NULL, not 0.
    SUM(CASE WHEN QUARTER(o.placed_at) = 1 THEN i.qty * i.unit_cents END) AS q1_cents,
    SUM(CASE WHEN QUARTER(o.placed_at) = 2 THEN i.qty * i.unit_cents END) AS q2_cents,
    SUM(CASE WHEN QUARTER(o.placed_at) = 3 THEN i.qty * i.unit_cents END) AS q3_cents,
    SUM(CASE WHEN QUARTER(o.placed_at) = 4 THEN i.qty * i.unit_cents END) AS q4_cents,
    SUM(i.qty * i.unit_cents)                                            AS year_cents
FROM order_items AS i
JOIN orders   AS o ON o.id = i.order_id
JOIN products AS p ON p.id = i.product_id
WHERE o.status = 'paid'
  -- Half-open range on the raw column so an index on placed_at stays usable;
  -- YEAR(o.placed_at) = 2025 would not.
  AND o.placed_at >= '2025-01-01'
  AND o.placed_at <  '2026-01-01'
GROUP BY p.id, p.name
ORDER BY year_cents DESC;

When to use it

  • Build a month-by-month or quarter-by-quarter revenue grid for a finance report.
  • Show counts per status as columns so one row summarises each queue or worker.
  • Produce a category-by-region crosstab for a dashboard whose categories rarely change.
  • Flatten wide legacy columns back into one row per value before loading them elsewhere.

More examples

Boolean shorthand for counting buckets

All three counts agree while result is never NULL: a comparison is 1 or 0, so SUM of it counts matches and AVG of it gives the proportion directly. Once result can be NULL they diverge — SUM(result = 'ok') skips those rows, the CASE with ELSE 0 counts them as failures. Decide which you mean rather than discovering it in a report.

Example · sql
SELECT
    DATE(created_at) AS day,
    COUNT(*)                                       AS attempts,
    SUM(result = 'ok')                             AS ok_short,
    SUM(CASE WHEN result = 'ok' THEN 1 ELSE 0 END) AS ok_long,
    COUNT(CASE WHEN result = 'ok' THEN 1 END)      AS ok_count,
    ROUND(100 * AVG(result = 'ok'), 1)             AS ok_pct
FROM login_attempts
WHERE created_at >= CURDATE() - INTERVAL 30 DAY
GROUP BY DATE(created_at)
ORDER BY day;

A dynamic pivot, and its cost

This is the only way to pivot an unknown column list inside MySQL, and it is worth seeing once so you recognise it. It also shows why it is a last resort: category names are interpolated straight into SQL, the result set's shape changes whenever a category is added, and you cannot write application code against a column list you do not know.

Example · sql
-- Default 1024 bytes will silently cut the column list short.
SET SESSION group_concat_max_len = 1024 * 1024;

SET @cols = (
    SELECT GROUP_CONCAT(
               CONCAT('SUM(CASE WHEN c.name = ''', c.name,
                      ''' THEN i.qty ELSE 0 END) AS `', c.name, '`')
               ORDER BY c.name SEPARATOR ', ')
    FROM categories AS c
);

SET @sql = CONCAT(
    'SELECT p.name, ', @cols, ' ',
    'FROM order_items AS i ',
    'JOIN products   AS p ON p.id = i.product_id ',
    'JOIN categories AS c ON c.id = p.category_id ',
    'GROUP BY p.id, p.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Unpivot: columns back into rows

The long form is what a normalised contact_methods table would have held, and producing it is the usual first step before grouping or charting. The cross-join version reads the table once instead of once per column, which matters when contacts is large; the UNION ALL version is easier to read and fine for small tables.

Example · sql
-- No UNPIVOT operator either; UNION ALL is the plain way.
SELECT id, 'email' AS channel, email AS address FROM contacts WHERE email IS NOT NULL
UNION ALL
SELECT id, 'phone', phone FROM contacts WHERE phone IS NOT NULL
UNION ALL
SELECT id, 'fax',   fax   FROM contacts WHERE fax   IS NOT NULL
ORDER BY id, channel;

-- One pass over contacts instead of three, using a small list of labels.
SELECT c.id, k.channel,
       CASE k.channel WHEN 'email' THEN c.email
                      WHEN 'phone' THEN c.phone
                      ELSE c.fax END AS address
FROM contacts AS c
JOIN (SELECT 'email' AS channel UNION ALL
      SELECT 'phone'           UNION ALL
      SELECT 'fax') AS k
HAVING address IS NOT NULL;

Related

sql casesql group bysql group concatsql filter clausesql sum avg

Discussion

  • Be the first to comment on this lesson.