How to Pivot Data in MySQL with Dynamic Column Names from a Single Column?

MySQL dynamic row value as column name pivot

Question:

In older MySQL tables, your data had mutable column names stored as unique values ​​in a single column named "name". You need a query that converts this data into a pivot format, where the name values ​​become the column headers and the corresponding field values ​​become the values ​​under those headers.

Solution:

Despite the lack of native support for pivots, MySQL can still achieve this through dynamic SQL and the GROUP_CONCAT() function.

MySQL query:

SELECT CONCAT(
  'SELECT `table`.id', GROUP_CONCAT('
     ,    `t_', REPLACE(name, '`', '``'), '`.value
         AS `', REPLACE(name, '`', '``'), '`'
     SEPARATOR ''),
 ' FROM `table` ', GROUP_CONCAT('
     LEFT JOIN `table`   AS `t_', REPLACE(name, '`', '``'), '`
            ON `table`.id = `t_', REPLACE(name, '`', '``'), '`.id
           AND `t_', REPLACE(name, '`', '``'), '`.name = ', QUOTE(name)
     SEPARATOR ''),
 ' GROUP BY `table`.id'
) INTO @qry FROM (SELECT DISTINCT name FROM `table`) t;

PREPARE stmt FROM @qry;
EXECUTE stmt;

Instructions:

1. The subquery selects different name values ​​and generates a UNION query to represent the different column names required for the pivot.
2. The CONCAT() function concatenates SQL strings and uses GROUP_CONCAT to dynamically connect column names with their values.
3. It creates a temporary stored procedure stmt using the generated SQL string.
4. EXECUTE stmt statement executes dynamic SQL queries, pivoting data as expected.

Result:

The query returns a pivoted result set with name values ​​as column headers and field values ​​as corresponding values:

id	timezone	language	country	something
0	Europe/London	en	45	x
1	Europe/Paris	fr	46	NULL

The above is the detailed content of How to Pivot Data in MySQL with Dynamic Column Names from a Single Column?. For more information, please follow other related articles on the PHP Chinese website!