MySQL: Transpose Rows to Columns

Transposing rows to columns in MySQL means reshaping data so that rows become columns, often to make it easier to read and analyze. This guide shows how to do it in SQL with conditional aggregation and the CASE statement.

Understanding the transpose operation

Converting rows into columns creates a pivot table effect. It’s useful when you want to compare values from different rows side by side in a horizontal format.

Sample dataset

Consider a simple dataset in a table sales_data:

CREATE TABLE sales_data (
    year INT,
    product VARCHAR(50),
    sales INT
);

Using CASE and GROUP BY

A common way to transpose rows to columns in MySQL is the CASE statement with GROUP BY. It works well when the distinct values are known and few.

Transposing specific rows to columns

This query puts sales for each product into its own column:

SELECT
    year,
    SUM(CASE WHEN product = 'Product A' THEN sales ELSE 0 END) AS ProductA_sales,
    SUM(CASE WHEN product = 'Product B' THEN sales ELSE 0 END) AS ProductB_sales
FROM
    sales_data
GROUP BY
    year;

Dynamic column generation

When the distinct values are unknown or numerous, you need a more complex approach that uses prepared statements.

Using prepared statements for dynamic transposing

Dynamic transposing takes two steps: build the list of columns dynamically, then construct a query from that list.

Generating the column list

Extract distinct values to be used as column names:

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
                 CONCAT(
                     'SUM(CASE WHEN product = ''',
                     product,
                     ''' THEN sales ELSE 0 END) AS ',
                     CONCAT('`',product,'_sales`')
                 )
    ) INTO @sql
FROM
    sales_data;

Building the dynamic query

Construct and execute a dynamic query with the generated column list:

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

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

If this query pattern is part of recurring reporting, Basedash helps you turn it into reusable, AI-native BI workflows: prompt-to-SQL, shared dashboards, and trusted answers that stay aligned with your data model.