MySQL Pivot: There Is No PIVOT, So Here Is What to Do Instead

Q: Should I use COUNT or SUM in a pivot?

SUMCASE WHEN cond THEN 1 ELSE 0 END. COUNTCASE WHEN cond THEN 1 ELSE 0 END counts every row, because 0 is not NULL and COUNT only skips NULLs - a common bug that produces plausible-looking wrong numbers.

MySQL does not have a PIVOT clause. SQL Server has one, Oracle has one, MySQL never added it, and it is not coming. What MySQL has instead is conditional aggregation - SUM(CASE WHEN ... THEN ... END) - which does exactly the same job with more typing and, honestly, more clarity about what is actually happening. If your columns are known ahead of time, this is a five-minute problem. If the columns depend on the data, you need dynamic SQL, and at that point you should ask whether the database is the right place to be doing this at all.

Conditional aggregation, the actual answer
Watch out for the aggregate you did not think about
When the columns are not known in advance
Just do it in the application
The performance side
How this fits the rest of the stack
FAQ

Conditional aggregation, the actual answer

Start with the shape everyone has: a long table of events, and a desire for a wide table of totals.

company_name | action | pagecount
Company A    | PRINT  | 3
Company A    | PRINT  | 2
Company A    | EMAIL  | NULL
Company B    | EMAIL  | NULL

You want one row per company, with a column per action. The pattern:

SELECT
  company_name,
  SUM(CASE WHEN action = 'PRINT' THEN 1 ELSE 0 END) AS print_count,
  SUM(CASE WHEN action = 'EMAIL' THEN 1 ELSE 0 END) AS email_count,
  SUM(CASE WHEN action = 'PRINT' THEN pagecount ELSE 0 END) AS print_pages
FROM actions
GROUP BY company_name;

That is a pivot. The GROUP BY collapses the rows; each CASE picks out the subset belonging to one output column; the aggregate function turns it into a value.

Swap SUM for whatever you need - COUNT, MAX, AVG. A common variant uses MAX to pull a single value rather than a total:

SELECT
  user_id,
  MAX(CASE WHEN attr = 'email' THEN value END) AS email,
  MAX(CASE WHEN attr = 'phone' THEN value END) AS phone
FROM user_attributes
GROUP BY user_id;

That is the standard trick for turning a key-value attribute table into proper columns. Note there is no ELSE - the CASE returns NULL for non-matching rows, and MAX ignores NULLs.

Watch out for the aggregate you did not think about

Two mistakes account for most broken pivot queries.

ELSE 0 versus no ELSE. With SUM, ELSE 0 is right - you want non-matching rows contributing zero. With MAX on a value column, you want no ELSE at all, so non-matching rows produce NULL and get ignored. Writing MAX(CASE WHEN ... THEN value ELSE 0 END) on a table of negative numbers will silently return 0 instead of the real maximum, and you will not notice for months.

COUNT counts NULLs the way you do not expect. COUNT(CASE WHEN action = 'PRINT' THEN 1 END) counts only matching rows, because COUNT skips NULL. But COUNT(CASE WHEN action = 'PRINT' THEN 1 ELSE 0 END) counts every row, because 0 is not NULL. This is a genuinely common bug and it produces numbers that look plausible.

The safe habit: use SUM(CASE WHEN cond THEN 1 ELSE 0 END) for counting. It reads slightly worse and it is never ambiguous.

When the columns are not known in advance

Conditional aggregation requires you to write one CASE per output column, which means you must know the columns when you write the query. If the columns come from the data - one column per product, per month, per status, where the set changes - you need to generate the SQL.

MySQL’s answer is to build the query text with GROUP_CONCAT and execute it with a prepared statement:

SET SESSION group_concat_max_len = 1000000;

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

SET @sql = CONCAT('SELECT company_name, ', @cols, ' FROM actions GROUP BY company_name');

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

This works. It is also the point at which you should stop and think, for three reasons.

group_concat_max_len defaults to 1024 characters. Exceed it and your generated SQL is silently truncated, producing a syntax error that points at a query you never wrote. This is the classic dynamic-pivot debugging experience.

It is a SQL injection vector. Those action values are being concatenated into executable SQL. If users can write arbitrary values into that column, they can write SQL into it. Escape rigorously or restrict the source column to a known-safe set.

The result set’s shape changes with the data. Your application receives a different number of columns depending on what happened to be in the table. Any ORM, any typed client, and any downstream consumer will hate this.

Just do it in the application

The unfashionable opinion that is usually correct: for dynamic pivots, do not pivot in SQL.

Fetch the long, narrow result - which is a simple, fast, indexable GROUP BY - and reshape it in code:

SELECT company_name, action, COUNT(*) AS n
FROM actions
GROUP BY company_name, action;

Then pivot it in Python, JavaScript, or whatever renders the report. pandas.pivot_table does this in one line. A dictionary keyed by company with a nested dict of actions does it in five. Your BI tool - Metabase, Looker, even a spreadsheet - does it natively, because reshaping is what those tools are for.

What you gain: no dynamic SQL, no injection surface, no truncation bug, a stable query the database can optimise and cache, and a result shape your ORM can actually type.

The honest guidance:

Fixed, known columns - conditional aggregation in SQL. Clean, fast, correct.
Dynamic columns - fetch long, pivot in the application layer.
Dynamic columns and you genuinely cannot touch the application - dynamic SQL, carefully, with a raised group_concat_max_len and paranoid escaping.

The number of teams who have built an elaborate stored-procedure pivot generator, when eight lines of application code would have done it, is not small.

The performance side

A pivot is a GROUP BY, and it obeys the normal rules.

The grouping column wants an index. If you are pivoting actions by company_name, an index on (company_name, action) lets MySQL scan in order and avoid a filesort - and if it covers every column the query touches, it can serve the whole thing from the index without reading the table.

Check what it is doing:

EXPLAIN SELECT company_name,
  SUM(CASE WHEN action = 'PRINT' THEN 1 ELSE 0 END) AS prints
FROM actions GROUP BY company_name;

Using temporary; Using filesort in the Extra column means MySQL is building a temp table and sorting it - fine on ten thousand rows, painful on ten million. An index on the grouping column usually removes both.

The other lever: aggregate less. If this is a report over historical data that does not change, do not recompute it on every page load. A summary table refreshed nightly turns a ten-second query into a ten-millisecond one, and reporting queries against immutable history are the ideal candidate for exactly that. The best pivot optimisation is frequently to not run the pivot at request time at all.

How this fits the rest of the stack

Whatever you decide here, the cost of the decision only shows up as a bill. The RunxBuild hosting calculator is the right place to model that before committing: the compute, the database, the storage, the bandwidth, the worker - each one is a separate line item, and the real cost of a platform is the sum, not the headline number. The RunxBuild dashboard is where the team sees the actual usage once it is running.

Useful related references:

FAQ

Does MySQL have a PIVOT clause?

No, and it is not planned. SQL Server and Oracle have one; MySQL never added it. The equivalent is conditional aggregation - SUM(CASE WHEN col = ‘x’ THEN 1 ELSE 0 END) with a GROUP BY - which does the same job explicitly.

How do I pivot when I do not know the columns in advance?

Either generate the SQL dynamically with GROUP_CONCAT and a prepared statement, or - usually better - fetch the long, narrow GROUP BY result and reshape it in your application or BI tool. Dynamic SQL brings a truncation bug, an injection surface, and a result shape that changes with the data.

Why is my dynamic pivot query truncated?

group_concat_max_len defaults to 1024 characters, so a generated column list longer than that is silently cut off, producing a syntax error in SQL you never wrote. Raise it with SET SESSION group_concat_max_len = 1000000 before building the query.

Should I use COUNT or SUM in a pivot?

SUM(CASE WHEN cond THEN 1 ELSE 0 END). COUNT(CASE WHEN cond THEN 1 ELSE 0 END) counts every row, because 0 is not NULL and COUNT only skips NULLs - a common bug that produces plausible-looking wrong numbers.

Is it faster to pivot in SQL or in the application?

For fixed columns, SQL is fine and often faster - it is one indexed GROUP BY. For dynamic columns, the application is usually better: you get a stable, cacheable query, no injection surface, and a result shape your ORM can type. Reshaping data is cheap in code and awkward in MySQL.