How to Do an Unpivot in MySQL
Unpivoting in MySQL involves transforming columns into rows, effectively normalizing data that was previously denormalized. This guide demonstrates how to perform an unpivot operation in MySQL, converting columnar data into a more query-friendly row format.
Understanding the unpivot operation
In MySQL, unpivoting is not a direct, single-command process as it is in some other SQL databases. Instead, it 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 such cases, consider automating this process with a dynamic SQL query or using an external tool to generate the query for you.
Enhancing query efficiency
- Index relevant columns to speed up the query, especially if the original table is large.
- Use
UNION ALL
instead ofUNION
to avoid the overhead of removing duplicate rows, assuming your data does not have duplicates.
Check out Basedash
While this guide covers the basics of unpivoting in MySQL, complex scenarios may require more advanced techniques or the use of additional tools to manage and transform your data efficiently. For extensive database management, including viewing and editing data, sharing access with team members, and creating charts and dashboards, you might find tools like Basedash particularly useful.
Invite only
Fast. Opinionated. Collaborative. Local-first. Keyboard centric. Crafted to the last pixel. We've got 50 slots for Alpha access.
How to Add Columns to MySQL Tables with ALTER TABLE
Robert Cooper
How to Add Columns to Your MySQL Table
Max Musing
Pivot Tables in MySQL
Robert Cooper
How to Rename a Table in MySQL
Max Musing
How to Optimize MySQL Tables for Better Performance
Robert Cooper
How to Display MySQL Table Schema: A Guide
Jeremy Sarchet