How to use GROUP_CONCAT in a CONCAT in MySQL

concatenation, group-concat, mysql

Solution

select id, group_concat(`Name` separator ',') as `ColumnName`
from
(
  select id, 
  concat(`Name`, ':', group_concat(`Value` separator ',')) as Name
   from mytbl group by id, Name
) tbl
group by id;

You can see it implemented here : Sql Fiddle Demo. Exactly what you need.

Explained by Splitting in Two Steps First we get a table having all values(comma separated) against a unique[Name,id]. Then from obtained table we get all names and values as a single value against each unique id See this explained here SQL Fiddle Demo (scroll down as it has two result sets)

Edit There was a mistake in reading question, I had grouped only by id. But two group_contacts are needed if (Values are to be concatenated grouped by Name and id and then over all by id). Previous answer was

select 
id,group_concat(concat(`name`,':',`value`) separator ',')
as Result from mytbl group by id

You can see it implemented here : SQL Fiddle Demo

Problem

If I have a table with the following data in MySQL: ``` id Name Value 1 A 4 1 A 5 1 B 8 2 C 9 ``` how do I get it into the following format? ``` id Column 1 A:4,5,B:8 2 C:9 ``` I think I have to use `GROUP_CONCAT`. But I'm not sure how it works.

Original source