Selecting multiple rows in a column into different columns (SQL)

Question

I have a table like this in MySQL called wp_usermeta: (it's a wordpress application but I dont want to interact with the database through wordpress, only through MySQL)

umeta_id    user_id     meta_key      meta_value
-------------------------------------------------------
1           1           nickname      Rymdblomma
2           1           firstname     Nile
3           1           lastname      White
4           1           description   Coolest person

And so forth. What I want is to select all of these 4, but put into 4 columns instead. At the same time, I am selecting data from other tables, creating a temporary table with all the attributes.

SELECT  wp_users.ID AS wp_id,
        wp_users.user_email AS email,
        wp_users.user_login AS username,
        wp_users.user_registered AS regDate,
        wp_blogs.path AS subname,
        wp_usermeta.meta_value AS firstname, 
        wp_usermeta.meta_value AS lastname
FROM    wp_users
INNER JOIN wp_blogs ON wp_users.ID = wp_blogs.blog_id
LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id AND 
(wp_usermeta.umeta_id = 2 OR wp_usermeta.umeta_id = 3);

( I want to include all the meta_values, not just first & lastname, but I've restricted it to 2 for testing purposes)

This returns something like this:

wp_id    email    username    regDate    subname    firstname    lastname
-------------------------------------------------------------------------
1        blabla   blabla      blabla     blabla     Nile         Nile
1        blabla   blabla      blabla     blabla     White        White

And so forth. But I dont want duplicate rows, instead I want something like this:

wp_id    email    username    regDate    subname    firstname    lastname
-------------------------------------------------------------------------
1        blabla   blabla      blabla     blabla     Nile         White

I have tried to search everywhere for any help but haven't found anything yet. Any help would be appreciated.

I do not see duplicates...

Maciej Los
– Maciej Los

2018-05-23 10:18:44 +00:00
Commented May 23, 2018 at 10:18 — Maciej Los
– Maciej Los, Commented May 23, 2018 at 10:18

butchyyyy · Accepted Answer · 2018-05-23 10:24:50Z

2

To get the firstname and lastname from wp_usermeta into one row, simply join the table twice with different umeta_id = join condition

SELECT  wp_users.ID AS wp_id,
        wp_users.user_email AS email,
        wp_users.user_login AS username,
        wp_users.user_registered AS regDate,
        wp_blogs.path AS subname,
        metafirstname.meta_value AS firstname, 
        metalastname.meta_value AS lastname
FROM    wp_users
INNER JOIN wp_blogs ON wp_users.ID = wp_blogs.blog_id
LEFT JOIN wp_usermeta metafirstname ON wp_users.ID = metafirstname.user_id AND metafirstname.umeta_id = 2
LEFT JOIN wp_usermeta metalastname ON wp_users.ID = metalastname.user_id AND metalastname.umeta_id = 3;

answered May 23, 2018 at 10:24

butchyyyy

3551 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lynx Over a year ago

Oh my, now I feel so stupid. Many many thanks, this solution fits (and works) perfectly!

Gordon Linoff · Accepted Answer · 2018-05-23 10:25:25Z

2

One method uses conditional aggregation:

SELECT u.ID AS wp_id, u.user_email AS email, u.user_login AS username,
       u.user_registered AS regDate,
       b.path AS subname,
       MAX(CASE WHEN um.umeta_id = 2 THEN um.meta_value END) as firstname,
       MAX(CASE WHEN um.umeta_id = 3 THEN um.meta_value END) as lastname
FROM wp_users u INNER JOIN
     wp_blogs b
     ON u.ID = b.blog_id LEFT JOIN
     wp_usermeta umf
     ON u.ID = um.user_id AND 
        um.umeta_id IN (2, 3)
GROUP BY u.ID, u.user_email, u.user_login,
         u.user_registered, b.path;

answered May 23, 2018 at 10:25

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Selecting multiple rows in a column into different columns (SQL)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related