Search code examples
mysqlsqlsqlyog

Group by ID and show multiple color of that specific ID

I am working with a new project where I met a new requirement, I am not a pro level query writer. There are IDs and those ids have multiple color the ID repeat it self where there are multiple color, what I want is to group those colors against that ID.

Particularly the client just want to see the it like that way and I am using SQLyog Ultimate.

Following is the table structure

...

product_id  color
1            Red
2            Blue
2            White
3            Red
3            Blue
3            Black
4            Purple
5            Yellow

...

I want it to be like

...

1 Red
2 Blue White
3 Red Blue Black
4 Purple
5 Yellow

...

Solution

  • In the MySQL query language, there is a function called GROUP_CONCAT which allows you to achieve what you want. Your query would be something like:

    select 
    product_id, 
    GROUP_CONCAT(color SEPARATOR ' ') 
from colors
group by product_id;

    Hope this helps :)