How to Dynamically Pivot Rows to Columns in MySQL Based on Multiple Criteria?

Dynamically Converting Rows to Columns with Multiple Criteria in MySQL

Question:

How can I dynamically convert rows to columns in MySQL based on two criteria, represented by two columns?

Context:

A table contains multiple rows with columns named "data" and "price" for each row. The goal is to transform the table into a new table with columns representing unique combinations of data and price, and each cell containing the corresponding row data.

Solution:

To dynamically convert rows to columns based on multiple criteria, we can employ a combination of MySQL functions and dynamic SQL.

1. Unpivot Data:

First, we unpivot the original table to create a new table with multiple rows for each unique combination of data and price.

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

2. Pivot Data:

Next, we aggregate the unpivoted data back into columns using the CASE statement.

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

3. Dynamic SQL:

To make the query dynamic, we use prepared statements and dynamically generate the CASE statements based on the unique columns in the original table.

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`) AS col
  FROM tableA
  UNION ALL
  SELECT CONCAT('item', `item`) AS col
  FROM tableA
) d;

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

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

This dynamic query handles an arbitrary number of unique criteria and produces the desired transformed table.

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