Pivoting Rows Into Columns in MySQL and SQL Server

Moving from a spreadsheet pivot to a database means choosing between two engines that do the job differently. MySQL leaves it to conditional aggregation, and SQL Server provides a PIVOT operator with its own quirks. This post shows both on the same data, then the dynamic version for when the set of columns is not fixed. It is a spoke of pivot tables and their SQL equivalents.

The data

A long table sales(month, product, units):

The target is one row per month with a column for each product.

MySQL: SUM and CASE

MySQL has no PIVOT. Use one conditional aggregate per output column:

SELECT month,
       SUM(CASE WHEN product = 'Pen' THEN units END) AS pen,
       SUM(CASE WHEN product = 'Pad' THEN units END) AS pad,
       SUM(units)                                    AS total
FROM sales
GROUP BY month
ORDER BY month;

+---------+------+------+-------+
| month   | pen  | pad  | total |
+---------+------+------+-------+
| 2025-01 |   10 |    4 |    14 |
| 2025-02 |   12 | NULL |    12 |
| 2025-03 | NULL |    7 |     7 |
+---------+------+------+-------+

A CASE with no ELSE returns null for other products, and SUM ignores nulls, so each column sums only its own product. A month with no Pen rows sums nothing and returns NULL. Wrap in COALESCE(..., 0) when zero is the correct reading. This pattern is the same on MariaDB, PostgreSQL, SQLite and Oracle (writing the same Excel pivot table as a GROUP BY query).

Two details: MySQL's SUM of an integer column returns a DECIMAL, which shows as 10 here but may carry a scale in clients; and sql_mode with ONLY_FULL_GROUP_BY is on by default, so every selected column must be in the GROUP BY or an aggregate. The query above satisfies that.

SQL Server: the PIVOT operator

SQL Server provides PIVOT in the FROM clause:

SELECT month, [Pen] AS pen, [Pad] AS pad
FROM (SELECT month, product, units FROM sales) AS src
PIVOT (
    SUM(units)
    FOR product IN ([Pen], [Pad])
) AS p
ORDER BY month;

Read it as: aggregate units with SUM, spread the values of product into columns, using the listed values. The list must be written out; it accepts literals only, not a subquery.

The quirk that causes most bugs is implicit grouping. PIVOT groups by every column in the source that is not the aggregated column or the spread column. That is why the source is a subquery with exactly month, product and units. If you pivot straight from a wider table, any extra column, such as an id or a timestamp, becomes part of the group and the pivot returns one row per id instead of one per month. Always select only what the pivot needs.

The conditional aggregate pattern works in SQL Server too, and some people prefer it because it allows several different aggregates and conditions in one pass, which PIVOT does not. PIVOT takes one aggregate only.

Dynamic columns in MySQL

When the product list changes, build the statement from the data. In MySQL, GROUP_CONCAT assembles the column expressions and a prepared statement runs the result:

SET SESSION group_concat_max_len = 100000;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('SUM(CASE WHEN product = ', QUOTE(product),
                ' THEN units END) AS `', REPLACE(product, '`', '``'), '`')
         ORDER BY product SEPARATOR ', ')
INTO @cols
FROM sales;

SET @sql = CONCAT('SELECT month, ', @cols, ' FROM sales GROUP BY month ORDER BY month');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

QUOTE() escapes the product value inside the CASE, and the REPLACE doubles any backtick in the alias, so a product with a quote or backtick in its name cannot end the string early. group_concat_max_len defaults to 1024 bytes and silently truncates a long list, which produces a broken statement; raise it for wide pivots.

Dynamic columns in SQL Server

SQL Server builds the list with STRING_AGG (2017 and later) and QUOTENAME, then runs it with sp_executesql:

DECLARE @cols nvarchar(max) =
  (SELECT STRING_AGG(QUOTENAME(product), ', ') WITHIN GROUP (ORDER BY product)
   FROM (SELECT DISTINCT product FROM sales) AS d);

DECLARE @sql nvarchar(max) = N'
SELECT month, ' + @cols + N'
FROM (SELECT month, product, units FROM sales) AS src
PIVOT (SUM(units) FOR product IN (' + @cols + N')) AS p
ORDER BY month;';

EXEC sp_executesql @sql;

QUOTENAME wraps each name in brackets and doubles any closing bracket inside it, which is what keeps a hostile category value from becoming SQL. Use it for every identifier you put into dynamic SQL. Never concatenate a raw value, even from your own table; a category column is user data like any other.

Pivoting one measure into several

If you need two measures per column, such as units and revenue for each product, the aggregate pattern scales:

SUM(CASE WHEN product = 'Pen' THEN units   END) AS pen_units,
SUM(CASE WHEN product = 'Pen' THEN revenue END) AS pen_revenue

PIVOT would need two separate pivots joined on the key. This is the case where the older pattern is simpler than the newer syntax.

Checks

Sum every output column and compare with a plain SELECT SUM(units) FROM sales. If a product exists that no column names, the totals will not match. A catch-all column, SUM(units) - SUM(CASE WHEN product IN ('Pen','Pad') THEN units END), shows what the fixed list misses. In SQL Server a value missing from the IN list is dropped without a warning, which is the most common cause of a pivot that is quietly short.

Choosing between CASE and PIVOT in SQL Server

SQL Server users have two ways and tend to pick the first one they learned. The PIVOT operator is shorter when you have one aggregate and a short, fixed list of values, and it reads well to people who know Excel. The CASE form is better in four situations: you need more than one aggregate per column, you want a different condition for each column that is not simple equality, you need the same SQL to run on another database later, or you want to add a total column in the same pass.

Both produce the same plan in most cases, so the choice is about readability and portability. If a team writes SQL for several engines, standardizing on conditional aggregation means one pattern to learn and one to review. If everything runs on SQL Server and the pivots are simple, the operator is fine, provided the source subquery selects only the columns the pivot needs.

Null handling and totals

Whichever method you use, decide how to show a missing combination. A null cell means no rows matched, a zero means rows summed to nothing. For counts, zero is usually right. For sums of money, many reports prefer a dash. Do the substitution in the presentation layer if you can, so the stored result keeps the distinction.

For row totals, add SUM(units) AS total as an ordinary aggregate. For a total row at the bottom, GROUP BY month WITH ROLLUP in MySQL adds a grand-total row with a null month, and GROUP BY ROLLUP (month) does the same in SQL Server. Label the null with COALESCE(month, 'Total'), but only after checking that no real month is null. Use GROUPING(month) in SQL Server to tell the two apart exactly.

When a SQL pivot is more work than it is worth

The reader uses Excel. A long result and a pivot table in Excel adapt to new categories with no code (building a pivot table from a database query).

The set of columns is large. Past a dozen, the table is too wide to read. Return rows and let a chart or client do the rotation.

The query runs on a schedule. Dynamic SQL is harder to review and test than static SQL. If the list is stable, keep it static and let a test fail when a new category appears.

You need to go the other way. Wide to long is a separate operation (unpivoting columns back into rows in SQL).

MySQL's reference for GROUP_CONCAT and Microsoft's page on FROM using PIVOT and UNPIVOT document the details. To format a long query, use the SQL formatter.