How to join multiple results into one row with dynamic columns? [duplicate]

Question

Foreword - I don't know how to formally describe the situation in a single question so the below is as verbose as I can make it...

Taking wordpress as an example, there's the "wp_users" table which has all the standard things like email and login and then there's the "wp_usermeta" table which looks like the following:

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

This table has many rows for each user with the meta_key and meta_value columns being the important bits of actual data.

What I'd like to do is query for all users and all meta, but only returning one row per user with every meta_key/meta_value as additional columns.

As an example scenario: there are three key/value pairs in the meta table, associated with the user_id 42. I'd like to query for the following result:

user_login,user_email,mk1,mk2,mk3
userone,one@example,"one","two","three"
usertwo,two@example,"yes","wow","moars"

Where "mk1", "mk2" and "mk3" are the values of the "meta_key" and the "one", "two", "three" are the corresponding "meta_value"s.

Can this be done in pure SQL (no procedures or code)?

Answer 1

One SQL sentence can't have variable fields per row; every row has same fields. But every field can have or haven't (null) value.

If you want that in pure SQL you can group_concat the fields of wp_usermeta, like:

SELECT use.name, use.email, group_concat(concat('{', meta_key, '##', meta_value, '}')) AS metadata
FROM wp_users use
INNER JOIN wp_usermeta met ON (use.user_id = met.user_id)
GROUP BY met.user_id

But then you need post-process outside Mysql.