CODEWITHSUNDEEP
mysqlsqlwordpresspivot-table
TTa
TTa

Reputation: 71

Create columns from row value pairs in SQL

Is it possible to roll up multiple rows into columns with the column name as one of the returned values without multiple joins?

Example:

Table: user

ID email name
1 [email protected] steve
2 [email protected] Jan

Table: meta

ID user_id meta_key meta_value
100 1 key_1 value_1
101 2 key_1 value_2
102 1 key_2 value_3
103 2 key_2 value_4

Desired query result:

ID email name key_1 key_2
1 [email protected] steve value_1 value_3
2 [email protected] jan value_2 value_4

Currently, I use multiple left joins to generate this query, but as the list of keys grows, it would be great to prevent having to use more and more left joins.

SELECT u.*, meta1.meta_value AS 'key_1', meta2.meta_value AS 'key_2'
FROM users as u
LEFT JOIN meta as meta1 ON meta1.user_id = u.ID
LEFT JOIN meta as meta2 ON meta2.user_id = u.ID
WHERE 1=1
AND meta1.meta_key = 'key_1'
AND meta2.meta_key = 'key_2'

I'd love to just be able to do something like the following which is obviously invalid in its current form

SELECT u.*, meta.meta_value as meta.meta_key
FROM users as u
LEFT JOIN meta as meta ON meta.user_id = u.ID
WHERE 1=1
AND meta.meta_key IN ('key_1', 'key_2')

My current compromise is that if the list of keys grows large, I can just return a cell that concats the key/value pairs.

For reference, this is a WordPress database.

Upvotes: 2

Views: 446

Answers (1)

lemon
lemon

Reputation: 15482

You can use:

  • CASE statements to extract your specific key values
  • MAX aggregation to remove your NULL values that your CASE statement will generate, hence grouping on the other selected column from the "user" table
SELECT u.*,
       MAX(CASE WHEN m.meta_key = 'key_1'
                THEN m.meta_value         END) AS key_1,
       MAX(CASE WHEN m.meta_key = 'key_2'
                THEN m.meta_value         END) AS key_2
FROM       user u
INNER JOIN meta m
        ON u.ID = m.user_id
GROUP BY u.ID,
         u.email,
         u.name

Check the demo here.

Upvotes: 1

Related Questions