Pivot Tables in MySQL

A pivot table in MySQL transforms rows into columns, which makes it a good way to generate reports. This post shows how it works so you can display cleaner data.

How to pivot a table in MySQL?

You can pivot in MySQL by using the CASE or IF statements within aggregation functions like SUM() or AVG(). This turns each unique value in a column into its own output column and aggregates the data based on your conditions.

Example

Consider a sales table with product_id, month, and sales_amount columns. To create a report that shows each product’s total sales for each month on a single row, use the following query:

SELECT
  product_id,
  SUM(CASE WHEN month = 'January' THEN sales_amount ELSE 0 END) AS January,
  SUM(CASE WHEN month = 'February' THEN sales_amount ELSE 0 END) AS February,
  SUM(CASE WHEN month = 'March' THEN sales_amount ELSE 0 END) AS March
FROM sales
GROUP BY product_id;

This query checks each row to assign its sales to the correct month, then sums the sales_amount for that month. The ELSE 0 part of the CASE statement makes a month with no sales display 0.

Dynamic pivot tables

To create a pivot table that adjusts to new data, such as additional months or categories, without manual updates, use dynamic SQL. The method involves:

1. Identifying unique values for column headers.
2. Building a SQL string with these values as conditional statements.
3. Executing the constructed SQL query.

This example pivots dynamically using prepared statements in MySQL:

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN month = ''',
      month,
      ''' THEN sales_amount ELSE 0 END) AS ',
      CONCAT('`', month, '`')
    )
  ) INTO @sql
FROM sales;

SET @sql = CONCAT('SELECT product_id, ', @sql, ' FROM sales GROUP BY product_id');

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

This approach collects every unique month from the sales table, builds a SQL query with a conditional sum for each one, and then executes it.