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.

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.