How to Dynamically Convert Rows to Columns in MySQL?

MySQL dynamic row to column query

MySQL supports converting row data into column data, allowing new columns to be dynamically added as needed. This process is often associated with pivot tables, which present data in a more user-friendly and tabular format. To achieve this conversion, you can use the GROUP BY and MAX functions.

The following is an example containing Table A and Table B:

<code>表 A
+--+-----+----+
|id|order|data|
+--+-----+----+
|1 |1    |P   |
+--+-----+----+
|2 |2    |Q   |
+--+-----+----+
|2 |1    |R   |
+--+-----+----+
|1 |2    |S   |
+--+-----+----+

表 B
+--+----+----+
|id|name|value|
+--+----+----+
|1 |name1|data1|
+--+----+----+
|1 |name2|data2|
+--+----+----+
|2 |name1|data3|
+--+----+----+
|2 |name2|data4|
+--+----+----+</code>

To convert this data structure into a table with rows and columns, you can use the following query:

SELECT  ID,
        MAX(IF(`order` = 1, data, NULL)) AS data1,
        MAX(IF(`order` = 2, data, NULL)) AS data2
FROM    TableA
GROUP   BY ID

The output of this query will be:

<code>+--+-----+-----+
|id|data1|data2|
+--+-----+-----+
|1 |P    |S    |
+--+-----+-----+
|2 |R    |Q    |
+--+-----+-----+</code>

As you can see, each row in Table B has been converted into a column in the resulting table.

If your data contains multiple values ​​for the same "order" field, you can combine dynamic SQL to handle the transformation more efficiently. This method involves dynamically generating queries based on different values ​​of the "order" field:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(`order` = ', `order`, ',data,NULL)) AS data', `order`)
  ) INTO @sql
FROM TableName;

SET @sql = CONCAT('SELECT  ID, ', @sql, ' 
                  FROM    TableName
                  GROUP   BY ID');

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

The advantage of this dynamic SQL approach is that there is no need to manually modify the query every time a new value is added to the "order" field.

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