How to Pivot Dynamic Row Values into Column Names in MySQL?

Convert dynamic row values ​​to column names using MySQL

In legacy tables with fixed structures, getting data in pivot format can be a challenge. MySQL lacks native pivot functions and requires the use of prepared statements to solve this problem.

Consider the following legacy table:

<code>id    name       value
------------------------------
0     timezone   Europe/London
0     language   en
0     country    45
0     something  x
1     timezone   Europe/Paris
1     language   fr
1     country    46</code>

Desired output, using dynamic row values ​​as column names:

<code>id    timezone       language    country  something
---------------------------------------------------
0     Europe/London  en          45       x
1     Europe/Paris   fr          46</code>

Unlike other RDBMS, MySQL requires an indirection approach. Building prepared statements is the preferred approach.

<code class="language-sql">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;</code>

This query uses GROUP_CONCAT() to build a dynamic SQL statement to connect the necessary JOINs and column aliases. The generated SQL statements are then prepared and executed.

This approach allows using unique row values ​​as variable column names without the need for hard-coded subselects or CASE statements. While it may require more setup, it provides a more flexible and efficient solution for pivoting data in MySQL.

The above is the detailed content of How to Pivot Dynamic Row Values into Column Names in MySQL?. For more information, please follow other related articles on the PHP Chinese website!