Merging multiple rows into one row and multiple columns on mysql

Question

I am working in MYSQL and need to extract user data to be pulled into a view. I will be using the data in an email client, so I cannot do this in the app layer.

The problem is that each data field for the user is contained in separate rows (this is how Wordpress sets up the data structure).

For example, wp_usermeta has multiple rows of data for each user like this:

user_id   meta_key       meta_data
   2      first_name     Jane
   2      last_name      Austin
   2      email          [email protected]
   3      first_name     Jack
   3      last_name      Palmer
   3      email          [email protected]

I need the data to be combined into one row, separate fields, like this:

user_id  first_name  last_name  email
2        Jane        Austin     [email protected]
3        Paul        Parker     [email protected]

I have searched around and cannot find this exact problem anywhere (I found a lot of concatenation, but that is not what I need).

Do you only care about firstName, lastName, and Email?

Adam Wenger
– Adam Wenger

2011-11-27 03:57:06 +00:00
Commented Nov 27, 2011 at 3:57 — Adam Wenger
– Adam Wenger, Commented Nov 27, 2011 at 3:57

Adam Wenger · Accepted Answer · 2011-11-27 04:02:52Z

14

If these are the only columns you are concerned with, this will work for you:

SELECT um.user_id
   , fn.meta_data AS first_name
   , ln.meta_data AS last_name
   , e.meta_data AS email
FROM wp_userMeta AS um
LEFT JOIN wp_user_Meta AS fn ON um.user_id = fn.user_id
   AND fn.meta_key = 'first_name'
LEFT JOIN wp_user_Meta AS ln ON um.user_id = ln.user_id
   AND ln.meta_key = 'last_name'
LEFT JOIN wp_user_Meta AS e ON um.user_id = e.user_id
   AND e.meta_key = 'email'

answered Nov 27, 2011 at 4:02

Adam Wenger

17.7k8 gold badges55 silver badges65 bronze badges

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

3 Comments

katemerart Over a year ago

Thanks for the quick response Adam. I tried to run the query, but all of the data fields are being populated by the column name, not the actual data. So all data for first_name is 'first_name', etc. SELECT um.user_id, fn.meta_key AS first_name, ln.meta_key AS last_name, e.meta_key AS email FROM wp_usermeta AS um LEFT JOIN wp_usermeta AS fn ON um.user_id = fn.user_id AND fn.meta_key = 'first_name' LEFT JOIN wp_usermeta AS ln ON um.user_id = ln.user_id AND ln.meta_key = 'last_name' LEFT JOIN wp_usermeta AS e ON um.user_id = e.user_id AND e.meta_key = 'email'

Adam Wenger Over a year ago

@katemerart Your SELECT is using meta_key instead of meta_data like my answer is suggesting. If you copy/paste my answer and run it you should get the results you desire.

katemerart Over a year ago

Adam - that did it - Thanks! I had to add DISTINCT to keep from getting multiple rows of the same data, but I got it working. Thank you so much!!!

JaskeyLam · Accepted Answer · 2015-06-03 04:46:14Z

4

Here is an another solution that doesn't need additional join operations, instead, we make use of group by statement along with case statement.

select um.user_id,
       max(case when um.meta_key ='first_name' then um.meta_data end) AS first_name,  
       max(case when um.meta_key ='last_name' then um.meta_data end) AS last_name  ,
       max(case when um.meta_key ='email' then um.meta_data end) AS email
from wp_usermeta um 
group by user_id;

Note the max function is just for making them into one row, you can use min as well.

Check SQL Fiddler Demo here

answered Jun 3, 2015 at 4:46

JaskeyLam

15.8k23 gold badges128 silver badges158 bronze badges

Comments

Mark Murphy · Accepted Answer · 2012-03-14 11:08:40Z

0

The previous answer and comments give the correct solution. Here is the working query (in schema for WP 3.1) for copy and paste...

SELECT distinct u.id, u.user_email,
   fn.meta_value AS first_name,
   ln.meta_value AS last_name
FROM wp_users AS u
LEFT JOIN wp_usermeta AS fn ON u.id = fn.user_id
   AND fn.meta_key = 'first_name'
LEFT JOIN wp_usermeta AS ln ON u.id = ln.user_id
   AND ln.meta_key = 'last_name'

answered Mar 14, 2012 at 11:08

Mark Murphy

1,6702 gold badges18 silver badges30 bronze badges

Collectives™ on Stack Overflow

Merging multiple rows into one row and multiple columns on mysql

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related