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`
) tbl
group by id;
You can see it implemented here : Sql Fiddle Demo. Exactly what you need.
Update 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
SELECT ID, GROUP_CONCAT(CONCAT_WS(':', NAME, VALUE) SEPARATOR ',') AS Result
FROM test GROUP BY ID
First of all, I don't see the reason for having an ID that's not unique, but I guess it's an ID that connects to another table. Second there is no need for subqueries, which beats up the server. You do this in one query, like this
SELECT id,GROUP_CONCAT(name, ':', value SEPARATOR "|") FROM sample GROUP BY id
You get fast and correct results, and you can split the result by that SEPARATOR "|". I always use this separator, because it's impossible to find it inside a string, therefor it's unique. There is no problem having two A's, you identify only the value. Or you can have one more colum, with the letter, which is even better. Like this :
SELECT id,GROUP_CONCAT(DISTINCT(name)), GROUP_CONCAT(value SEPARATOR "|") FROM sample GROUP BY name
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 A
GROUP BY ID;
SQL Fiddle: http://sqlfiddle.com/#!2/b5abe/9/0