How to Convert Rows into Columns in MySQL?

Converting rows into columns, also known as pivoting or transposing, is a common operation in DBMS, and MySQL provides robust functionality for achieving this transformation.

This process is useful to reshape data for better analysis or reporting. This guide will explore the syntax, usage, and examples of converting rows into columns in MySQL.

Transpose Rows into Columns in MySQL

Transposing rows into columns means, rearranging a table such that the columns become the rows, and rows become the columns. It is useful in data analysis to organize data for effective reporting or visualization.

The basic syntax to convert rows into columns in MySQL involves using the CASE statement in combination with the MAX function.

Syntax

SELECT
MAX(CASE WHEN condition1 THEN value1 END) AS column1,
MAX(CASE WHEN condition2 THEN value2 END) AS column2,
FROM your_table
GROUP BY common_column;

• Conditions and Values: Define conditions based on which the rows will be converted into columns. Specify the corresponding value to be displayed in the new column for each condition.
• GROUP BY: Use the GROUP BY clause to group the results by a common column. This column helps aggregate rows into distinct sets with each set corresponding to a new column in the result.

Demo MySQL Database

For Understanding, how to convert rows into columns in MySQL, we will use the following table in our examples.

To create this table on your MySQL Workbench, Copy-Paste and Run the following MySQL queries:

CREATE DATABASE Convert_db;
USE Convert_db;

CREATE TABLE sales_data (
    product VARCHAR(50),
    month VARCHAR(20),
    revenue INT
);

INSERT INTO sales_data (product, month, revenue) VALUES
('A', 'January', 1000),
('A', 'February', 1200),
('B', 'January', 800),
('B', 'February', 900),
('C', 'January', 1500),
('C', 'February', 1800);

Transforming Rows into Columns in MySQL Examples

Let's look at some examples on how to transform rows into columns in MySQL:-

Example 1: Simple Conversion

In this example, we want to pivot the data to display monthly revenues for each product as columns. This is static pivot and can be done with following MySQL query.

SELECT product,
MAX(CASE WHEN month = 'January' THEN revenue END) AS January,
MAX(CASE WHEN month = 'February' THEN revenue END) AS February
FROM sales_data
GROUP BY product;

Output:

Explanation: The SQL query retrieves the maximum revenue for each product in January and February from the sales_data table. The output displays a summary with columns for each month, showing the highest revenue achieved by each product during the specified months. The result is grouped by the product.

Example 2: Dynamic Pivot

In some cases, you might not know the exact values for pivoting in advance. Here's an example using a dynamic pivot based on distinct months with MySQL query:

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
           'MAX(CASE WHEN month = "', month, '" THEN revenue END) AS "', month, '"')
INTO @sql
FROM sales_data;

SET @sql = CONCAT('SELECT product, ', @sql, ' FROM sales_data GROUP BY product;');

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

Output:

Explanation: The SQL code dynamically generates a pivot table, transforming the sales_data table. It sets the group_concat_max_len session variable, creates a dynamic SQL statement to pivot the data, and then executes it. The output displays a summary with product-wise maximum revenue for each month, effectively transposing the original data.

Conclusion

Converting rows into columns in MySQL is a powerful technique to reshape your data for better analysis. By using the CASE statement along with aggregate functions like MAX, you can efficiently pivot your data. Whether you need a static or dynamic pivot, MySQL provides the flexibility to transform your dataset to meet your analytical requirements.