How to Unpivot Table in MySQL

Sometimes, while working with database tables, MySQL developers need to convert one or more of its columns into rows. In other words, you need to transform a wide table into a long table, for easier analysis. This is known as Unpivot. Unfortunately, there is no direct UNPIVOT clause or function in MySQL. So we will need to construct an SQL query for this purpose. In this article, we will learn how to unpivot table in MySQL.

What is Unpivot in MySQL

Unpivot table is a process where several columns of a table are converted into rows, changing a wide table into a long one. Here is an example of table where monthly sales values are stored as separate columns.

Product	Jan_Sales	Feb_Sales	Mar_Sales
A	100	110	90
B	90	100	110

It is difficult to aggregate product sales using the above table. Also, the number of columns in table will keep increasing as we keep adding more sales data to it. This makes it difficult to manage data. Here is an example of the unpivoted version of above table.

Product	Month	Sales
A	Jan	100
A	Feb	110
A	Mar	90
B	Jan	90
B	Feb	100
B	Mar	110

Unpivoting table is commonly required in data analysis and reporting. For example, when you unpivot table, some of its column names become row values. This makes it easy to aggregate data and generate reports.

For example, in the above table, you can easily calculate product wise sales.

Product	Sales
A	300
B	300

How to Unpivot Table in MySQL

There are several different ways to unpivot table in MySQL. Let us look at them one by one.

Single Column

The most basic way to unpivot table is to use UNION ALL. Here is the SQL query to unpivot our above table.

SELECT Product, 'Jan' AS Month, Jan_Sales AS Sales
FROM sales
UNION ALL
SELECT Product, 'Feb' AS Month, Feb_Sales AS Sales
FROM sales
UNION ALL
SELECT Product, 'Mar' AS Month, Mar_Sales AS Sales
FROM sales;

Here is the output.

Product	Month	Sales
A	Jan	100
A	Feb	110
A	Mar	90
B	Jan	90
B	Feb	100
B	Mar	110

In the above query, we create separate sub queries to get the sales result of each month and use UNION ALL clause to combine them. In each query, we store the month name under Month column and the sales value under Sales column.

Multiple Columns

In the above example, we are only unpivoting a single column, sales. You can also use the above approach to unpivot multiple columns. Let us say you have the following table with 2 columns sales and orders that are pivoted.

Product	Jan_Sales	Feb_Sales	Mar_Sales	Jan_Orders	Feb_Orders	Mar_Orders
A	100	120	90	10	9	11
B	50	60	70	3	5	4

Here is the SQL query to easily unpivot both sales as well as orders using a single query.

SELECT Product, 'Jan' AS Month, Jan_Sales AS Sales, Jan_Orders as Orders
FROM sales
UNION ALL
SELECT Product, 'Feb' AS Month, Feb_Sales AS Sales, Feb_Orders as Orders
FROM sales
UNION ALL
SELECT Product, 'Mar' AS Month, Mar_Sales AS Sales, Mar_Orders as Orders
FROM sales;

Here is the output you will see.

Product	Month	Sales	Orders
A	Jan	100	10
A	Feb	120	9
A	Mar	90	11
B	Jan	50	3
B	Feb	60	5
B	Mar	70	4

In the above query, we create separate sub queries to get each month’s sales and orders and use UNION ALL to append it.

Stored Procedure

If you need to unpivot data frequently, then you can create a stored procedure for this purpose. Here is an example to create stored procedure named unpivot_data().

DELIMITER $$

CREATE PROCEDURE unpivot_data()
BEGIN
    SELECT Product, 'Jan' AS Month, Jan_Sales AS Sales FROM sales
    UNION ALL
    SELECT Product, 'Feb' AS Month, Feb_Sales AS Sales FROM sales
    UNION ALL
    SELECT Product, 'Mar' AS Month, Mar_Sales AS Sales FROM sales;
END$$

DELIMITER ;

In the above query, we place our earlier SQL query within BEGIN…END block. We can call this procedure as shown.

call unpivot_data();

Dynamic SQL

All the above queries work when the number of columns and their names are fixed, and known in advance. But what if you do not know all column names, or if the number of columns keep increasing as more data gets added to the table? In such cases, we need to use a dynamic SQL query. Here is an example to use Dynamic SQL query to unpivot a varying number of columns.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
    CONCAT(
      'SELECT Product, ''', column_name, ''' AS Month, ', column_name, ' AS Sales FROM sales'
    )
  ) INTO @sql
FROM information_schema.columns
WHERE table_name = 'sales' AND column_name LIKE '%_Sales';

SET @sql = CONCAT(@sql, ' ORDER BY Product');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

In the above query, we use group_concat and concat functions to create dynamic SQL query to unpivot our tables. We get the list of column names from information_schema.columns table.

Tips & Best Practices

Here are some of the things to keep in mind while unpivoting table.

1. When you use UNION or UNION ALL clause, ensure that the columns being combined, have the same data type.

2. Only Unpivot necessary columns. If unnecessarily unpivot all columns, then some of the column values will be duplicated as row values.

3. Please note, unpivoting and pivoting are process-heavy operations. So the bigger your table, the longer it will take to unpivot it. In such cases, it is better to use indexes.

4. When you unpivot table, it may result in new columns having NULLs and duplicate values.

5. Label your new columns and rows properly so that the data is aligned properly, while using UNION ALL. Otherwise, it will create separate columns for different column names.

Conclusion

In this article, we have learnt several different ways to unpivot table in MySQL database. We learnt how to unpivot single as well as multiple columns using UNION ALL. We also learnt how to create STORED PROCEDURE to frequently call these queries without retyping them. You can use any of these methods to transform columns into rows and convert a wide table into a long one. Unpivoting a table is very useful in data aggregation and analysis. It is commonly required by business analysts and data scientists.

Also read:
How to Alter Column from Null to Not Null
How to Import SQL File in MySQL
How to Concatenate Multiple SQL Rows in MySQL