How to Pivot a MySQL Table with Dynamic Column Names from Row Values?

MySQL: Dynamic row values ​​as column names

Question:

How to build a MySQL query that converts an old table with variable column names to a table with fixed column names, where each unique row value for a specific column becomes a column name?

Background:

Consider a table with the following structure:

<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>

Requirements:

Convert the table to the following format:

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

MySQL does not support native pivot operations, so we need to find a workaround.

Solution:

The
• GROUP_CONCAT() function can be used to dynamically generate queries that create the desired table format.
• The first query prepares a string that uses GROUP_CONCAT() to build the final query.
• The second query uses PREPARE and EXECUTE to execute a dynamically generated query.

Query:

<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>

Note:

• GROUP_CONCAT() The length of the result is limited by the group_concat_max_len variable (default is 1024 bytes).
• Alternatively, one can use prepared statements with CASE WHEN or multiple subselects/joins, but this requires manual handling of unique column values.

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