How do I SELECT a "fake" column/value based on joined table values?

inner-join, mysql, select, wordpress

Solution

MySQL has no pivot function. If you know already all of the possible values of meta_key, you could use a hard-coded query like this:

SELECT
  wp_users.*,
  MAX(CASE WHEN `meta_key`='first-name' THEN meta_value END) AS `first-name`,
  MAX(CASE WHEN `meta_key`='last-name' THEN meta_value END) AS `last-name`,
  ....
FROM
  wp_users INNER JOIN wp_usermeta
  ON wp_users.ID=wp_usermeta.user_id
GROUP BY
  wp_users.ID, wp_users.username, wp_users.email;

Or you could create your SQL query dynamically, using a prepared statement, like this:

SELECT
  CONCAT(
    'SELECT wp_users.*,',
    GROUP_CONCAT(
      CONCAT(
        'MAX(CASE WHEN `meta_key`=\'',
        meta_key,
        '\' THEN meta_value END) AS `',
        meta_key,
        '`')),
    ' FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID=wp_usermeta.user_id',
    ' GROUP BY wp_users.ID, wp_users.username, wp_users.email')
FROM
  (SELECT DISTINCT meta_key FROM wp_usermeta) s
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

Please see fiddle here.

Problem

Short Version: I need to customize the result of a query, using one value as the column name, and another value as the column's value. It also needs to be automatic (not hard coded, hundreds of possible "meta keys"). Long Version: I'm using Wordpress's "user meta" structure, where a single user has a user ID, and another table contains "meta-data" which is related to the user. This meta data is arbitrary, and only has four columns: `meta_id`, `post_id`, `meta_key`, `meta_value`. I would like to do an SQL query for a certain user, and format the results so that all of the metadata is formatted as if they were rows, where the `meta_key` is a column name, and `meta_value` is the value for the column. Example database: ``` -- wp_users * ID | username | email 35 | radgh | radgh@example.org -- wp_usermeta * meta_id | user_id | meta_key | meta_value 1 | 35 | first-name | Radley 2 | 35 | last-name | Sustaire 3 | 35 | newsletter | on ``` My Query (I need the (???) portion figured out, which would generate the meta key/value pairs as fake columns) ``` SELECT `users`.ID as 'user_id', `users`.username as 'username', `users`.email as 'email', (???) FROM `wp_users` `users` INNER JOIN `wp_usermeta` `meta` ON `users`.ID = `meta`.user_id WHERE `wp_users`.ID = 35 ``` Desired Outcome: ``` * user_id | username | email | first-name | last-name | newsletter 35 | radgh | radgh@example.org | Radley | Sustaire | on ``` In this similar question the selected answer uses hard coded fields. I would like to do this automatically. :)

Original source

Related problems