How to Do an Unpivot in MySQL

Unpivoting in MySQL involves transforming columns into rows, which normalizes data that was previously denormalized. This guide shows how to unpivot in MySQL, converting columnar data into a row format that’s easier to query.

Understanding the unpivot operation

Some SQL databases can unpivot with a single command, but MySQL requires a combination of SQL operations to achieve the same result. The goal is to transform data from a wide format (with many columns) to a long format (with more rows and fewer columns).

Preparing your data

Consider a table named sales_data with the following structure:

| product_id | Jan_sales | Feb_sales | Mar_sales |
|------------|-----------|-----------|-----------|
| 1          | 150       | 200       | 250       |
| 2          | 300       | 350       | 400       |

Our aim is to unpivot the monthly sales columns into a format with two columns: month and sales.

Creating the unpivot query

The following query uses a combination of UNION ALL and SELECT statements to unpivot the data:

SELECT product_id, 'Jan' as month, Jan_sales as sales FROM sales_data
UNION ALL
SELECT product_id, 'Feb', Feb_sales FROM sales_data
UNION ALL
SELECT product_id, 'Mar', Mar_sales FROM sales_data;

This query produces an output like:

| product_id | month | sales |
|------------|-------|-------|
| 1          | Jan   | 150   |
| 1          | Feb   | 200   |
| 1          | Mar   | 250   |
| 2          | Jan   | 300   |
| 2          | Feb   | 350   |
| 2          | Mar   | 400   |

Handling large numbers of columns

If your table has a large number of columns, manually writing a UNION for each one can be impractical. In that case, consider generating the query with dynamic SQL or an external tool.

Enhancing query efficiency

• Index relevant columns to speed up the query, especially if the original table is large.
• Use UNION ALL instead of UNION to avoid the overhead of removing duplicate rows, assuming your data does not have duplicates.

If this query pattern is part of recurring reporting, Basedash helps you turn it into reusable, AI-native BI workflows: prompt-to-SQL, shared dashboards, and trusted answers that stay aligned with your data model.