1

This question already has an answer here:

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

marked as duplicate by Andriy M, Philᵀᴹ, Vérace, Michael Green, James Anderson Jun 27 '16 at 11:39

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.