mysql join many to one table and display each ‘many’ record as an additional column

Posted on

Question :

I have a many to one table.

For example:

user_id, name
1, chris
2, john


user_id, item
1, apple, 
1, banana,
2, orange

How could I run a query that returns something like this:

user_id, item_1, item_2, item_3
1, apple, banana, null
2, orange, null, null

Would I need to do a sub query for each item_n column?

Answer :

As Kondybas suggests in his comment, you can use GROUP_CONCAT to concatenate the columns

SELECT user_id, GROUP_CONCAT(item,',') as items 
FROM table_name 
GROUP BY user_id

Note that all original columns is fit into one column in the answer.

Leave a Reply

Your email address will not be published. Required fields are marked *