mySql comibe multiple row as comma seperated

 To combine rows as comma separated for a column instead of multiple rows we will use GROUP_CONCAT function with that column, but all the remaining columns will be in group by clause. below is the query for products with multiple colors for a single product. we will combine colors column as comma separated in the below query.

select DISTINCT p.id as product_id , p.name as title, p.sku, p.material, 

p.feature_image_path, feature_image_name ,

                GROUP_CONCAT(c.name) color  -- it will combile multi values as comma seperated rather than multiple rows but remaining columns will be in group by

from 

products p

inner join color_product cp on p.id=cp.product_id

inner join colors c on cp.color_id=c.id

inner join categories ct on p.category_id=ct.id

where p.name  LIKE CONCAT("%",'red',"%") OR

  p.sku  LIKE CONCAT("%",'red',"%") OR

      p.description  LIKE CONCAT("%",'red',"%") OR

      p.material  LIKE CONCAT("%",'red',"%") OR

      c.name   LIKE CONCAT("%",'red',"%") OR

      ct.name   LIKE CONCAT("%",'red',"%")

group by

p.id , p.name, p.sku, p.material, 

p.feature_image_path, feature_image_name