mysql select dynamic row values as column names, another column as value

Question

I have a legacy table of user information (that is still in active use) and I cannot change the structure of -

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

timezone/language/country etc are only examples of names, they can be variable/there is no finite list other than unique on rows of that column

I need a MySQL compatible SQL query that would return -

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

I've looked through various answers on stackoverflow around hacking Pivot table functionality in to MySQL, and similar but none of them seem to match this case of using variable column name alias from unique row values from a column of the same table. Although I have had little sleep so they're all starting to become a bit of a blur, apologies in advance.

Closest I could find would be to use prepared statements https://stackoverflow.com/a/986088/830171 which would first get all possible/unique values from name column and build a query that uses CASE WHEN, and/or multiple sub-SELECT or JOIN on same table queries.

The alternatives I can think of would be to get all rows for that user id and process them in the application itself in a for-loop, or attempt to limit the names to a finite amount and use sub-SELECTs/JOINs. However that second option is not ideal if a new name is added I'd have to revisit this query.

Please tell me I've missed something obvious

Answer 1

Unlike some other RDBMS MySQL doesn't have native support for pivoting operations of this sort by design (the developers feel it's more suited to the presentation, rather than database, layer of your application).

If you absolutely must perfom such manipulations within MySQL, building a prepared statement is the way to go—although rather than messing around with CASE, I'd probably just use MySQL's GROUP_CONCAT() function:

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;

See it on sqlfiddle.

Note that the result of GROUP_CONCAT() is limited by the group_concat_max_len variable (default of 1024 bytes: unlikely to be relevant here unless you have some extremely long name values).