How to Dynamically Pivot Rows into Columns in MySQL?

MySQL dynamic row and column conversion skills

In MySQL, dynamically converting rows into columns, while dynamically adding columns to accommodate new rows, can be achieved using a variety of techniques.

Use GROUP BY and MAX functions

One way is to utilize the GROUP BY and MAX functions. For example, consider a table with rows and columns like this:

<code>表A

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

To convert these rows into columns, you can use the following query:

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

This query groups the rows based on the ID column and returns the corresponding value for each order column using MAX and IF statements. The result will be:

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

Use dynamic SQL

If the order column has multiple values, it may be more suitable to use dynamic SQL. This allows queries to be generated dynamically and accommodate different order quantities.

<code>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;</code>

By dynamically generating queries based on different order values, this approach provides flexibility and ensures that the result table will automatically adapt to any changes in the data.

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