How to use GROUP_CONCAT in a CONCAT in MySQL

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`) tblgroup by id;

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

UpdateSplitting 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 idSee 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

Try:

CREATE TABLE test (  ID INTEGER,  NAME VARCHAR (50),  VALUE INTEGER);INSERT INTO test VALUES (1, 'A', 4);INSERT INTO test VALUES (1, 'A', 5);INSERT INTO test VALUES (1, 'B', 8);INSERT INTO test VALUES (2, 'C', 9);SELECT ID, GROUP_CONCAT(NAME ORDER BY NAME ASC SEPARATOR ',')FROM (  SELECT ID, CONCAT(NAME, ':', GROUP_CONCAT(VALUE ORDER BY VALUE ASC SEPARATOR ',')) AS NAME  FROM test  GROUP BY ID, NAME) AS AGROUP BY ID;

SQL Fiddle: http://sqlfiddle.com/#!2/b5abe/9/0

SELECT ID, GROUP_CONCAT(CONCAT_WS(':', NAME, VALUE) SEPARATOR ',') AS Result FROM test GROUP BY ID