concat string in mysql record wise

Question

My current query:-

select
p.id as product_id,
p.product_name,
child.name as category_name,
parent.name as parent_category_name
from category child 
left join products p on FIND_IN_SET(child.id,p.category_ids)
left join category parent on parent.id = child.parentid and parent.parentid = 0
where p.id = 7

Current Results:-

product_Id | product_name | category_name | parent_category_name
   7           ponds          cream              chemical
   7           ponds          medicine           chemical
   7           ponds          powder             Ayurvedic

Now I would like to group by product id and the child/parent category should be show in comma separated format like "cream,medicine,powder". So here the number of record would be only one like this :-

Expected Results:-

product_Id | product_name | category_name        |   parent_category_name
   7           ponds       cream,medicine,powder  chemical,chemical,Ayurvedic

I don't know its possible or not in mysql query.

Any suggestion or ideas would be greatly appreciated.

Thanks a lot.

Just so long as you don't care about the order in the group concat... for that see-->http://stackoverflow.com/questions/995373/mysql-sort-group-concat-values — xQbert, Dec 13 '11 at 12:03

Michael Berkowski · Accepted Answer · 2011-12-13T12:15:48.330

1

This will work using GROUP_CONCAT() and grouping by product_id and product_name. Since your existing query already pulls the necessary columns and rows, you need only to apply GROUP_CONCAT() and add a GROUP BY clause:

select
  p.id as product_id,
  p.product_name,
  /* GROUP_CONCAT() */
  GROUP_CONCAT(DISTINCT child.name) as category_name,
  GROUP_CONCAT(DISTINCT parent.name) as parent_category_name
from category child 
  left join products p on FIND_IN_SET(child.id,p.category_ids)
  left join category parent on parent.id = child.parentid and parent.parentid = 0
where p.id = 7
/* Add a GROUP BY clause */
GROUP BY 
  product_id,
  product_name

edited Dec 13 '11 at 12:15

answered Dec 13 '11 at 11:57

Michael Berkowski

267,341
46
444
390

Thanks Nice, Michael :) And also i want to remove duplicate values, like see in parent_category_name there is chemical is duplicate, So i want to list only 'Chemical,Ayurvedic' ? – Bajrang Dec 13 '11 at 12:02

score 1 · Answer 2 · answered Dec 13 '11 at 11:57

1

Use GROUP_CONCAT() for this.

SELECT `product_Id`, GROUP_CONCAT(`category_name`)
FROM ...
GROUP BY `product_Id`

answered Dec 13 '11 at 11:57

concat string in mysql record wise

2 Answers2