In data analysis, it’s common to encounter data that is structured in a wide format, with values spread across multiple columns. While this format can be convenient for certain types of analysis, there are times when you need to reshape your data into a longer, more normalized format—this process is known as “unpivoting.” In MySQL, unpivoting data is not as straightforward as it is in some other SQL databases, but with the right techniques, it can be done efficiently.
In this article, we’ll explore how to unpivot data in MySQL, covering various methods and best practices. Whether you’re cleaning up data for analysis, preparing it for reporting, or just trying to make your database more flexible, understanding how to unpivot in MySQL will be a valuable skill.
CREATE
PROCEDURE
UnpivotSalesData()
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 ;
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;
