How to Convert MySQL Rows to Columns Based on Multiple Columns?

MySQL Query to Convert Rows to Columns Based on Multiple Columns

Introduction:

Dynamically converting rows to columns is a common task in data manipulation. In this article, we explore a solution using a MySQL query, extending the capabilities of a previous solution that handled a single column conversion.

Problem:

You have a table with multiple rows representing data and corresponding prices, organized by order and item. The goal is to transform this data into a table where each order is represented as a column and each item is represented as a row, with the corresponding prices displayed.

Solution:

To achieve this multi-column conversion, we employ a two-step process.

Step 1: Unpivoting the Data

We begin by unpivoting the data using UNION ALL to create multiple rows for each order and item combination. This allows us to manipulate the data more easily in the next step.

SELECT id, CONCAT('order', `order`) col, data value
FROM tableA
UNION ALL
SELECT id, CONCAT('item', item) col, price value
FROM tableA;

Step 2: Pivoting the Unpivoted Data

Once the data is unpivoted, we pivot it back into columns using aggregate functions with CASE statements. This groups the unpivoted rows by ID and assigns the corresponding values to the appropriate columns.

SELECT
  id, 
  MAX(CASE WHEN col = 'order1' THEN value END) ORDER1,
  MAX(CASE WHEN col = 'order2' THEN value END) ORDER2,
  MAX(CASE WHEN col = 'order3' THEN value END) ORDER3,
  MAX(CASE WHEN col = 'item1' THEN value END) ITEM1,
  MAX(CASE WHEN col = 'item2' THEN value END) ITEM2,
  MAX(CASE WHEN col = 'item3' THEN value END) ITEM3
FROM
(
  SELECT id, CONCAT('order', `order`) col, data value
  FROM tableA
  UNION ALL
  SELECT id, CONCAT('item', item) col, price value
  FROM tableA
) d
GROUP BY id;

Dynamic Query Generation:

To handle tables with varying numbers of orders and items, we can generate the query dynamically using a prepared statement.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN col = ''',
      col,
      ''' THEN value END) AS `', 
      col, '`')
  ) INTO @sql
FROM
(
  SELECT CONCAT('order', `order`) col
  FROM tableA
  UNION ALL
  SELECT CONCAT('item', `item`) col
  FROM tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM
                  (
                    SELECT id, CONCAT(''order'', `order`) col,  data value
                    FROM tableA
                    UNION ALL
                    SELECT id, CONCAT(''item'', item) col, price value
                    FROM tableA
                  ) d
                  GROUP BY id');

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

Result:

The resulting table provides a concise representation of the data, with each order as a column and each item as a row, displaying the corresponding prices.

The above is the detailed content of How to Convert MySQL Rows to Columns Based on Multiple Columns?. For more information, please follow other related articles on the PHP Chinese website!