Select Multiple Values from Same Column; one sql statment

Question

I am looking to return a list of users from a database. There are hundreds of users in the db. Each user has it's own unique user_id. All of the values that I am trying to return reside in the same column (meta_value).

The database structure is as follows:

id | user_id | meta_key | meta_value

sample data is as follows:

1 | 3434 | first_name | Brandon
2 | 3434 | last_name | Johnson
3 | 3434 | street_add | 123 main
4 | 3434 | city | ocean beach
5 | 3434 | state | Texas

I am trying to return the first name, last name, street_add, city, and state in one sql statement.

I'd like the output to look like:

Brandon, Johnson, 123 Main, Ocean Beach, Texas 

• Mihai: Do all user_ids respect the same order,first comes firstname than lastname and so on,ordered by id?You could use SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) FROM t GROUP BY user_id
• Michael Green: When you say "one query" do you mean "one row?" An example of your expected output would be helpful.
• brandon-estrella-dev: Michael Green - I am just looking to pull all the records in a single sql statement. I updated the post to show the desired output. I'd like to pull all the user's info from the database in the output like above to export to an excel sheet
• brandon-estrella-dev: Mihai - There are more records in the meta_value column than just first name, last name, address, city, state and zip. However, those are the only records I am looking to pull.
• Mihai: SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) FROM t WHERE meta_key IN('first_name','last_name',etc) GROUP BY user_id

Answer 1

Just use GROUP_CONCAT with a WHERE condition

SELECT user_id,GROUP_CONCAT(meta_value ORDER BY id) 
FROM t 
WHERE meta_key IN('first_name','last_name','street_add','city','state') 
GROUP BY user_id