How to use GROUP_CONCAT in a CONCAT in MySQL

Question

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.

Sami · Accepted Answer · 2023-07-04T07:24:28.723

179

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

edited Jul 04 '23 at 07:24

answered Nov 19 '12 at 10:33

Sami

8,168
9
66
99

This does not give what Biswa asked for. – eisberg Nov 19 '12 at 10:34
3

I think its important to warn peoples that using only one kind of separator could be disadvantageous. I suggest making the "name" separator as semicolon ( ; ), and the values separator can remain as comma ( , ) – Fandi Susanto Jul 28 '15 at 06:46
6

Please also note that `GROUP_CONCAT` might silently truncate its output to `group_concat_max_len`. `SET group_concat_max_len=...` will help, but it's a good idea anyway to check that the returned (byte?) length is less than `group_concat_max_len`. – tuomassalo Dec 05 '16 at 09:37
Ref. for my future self: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat – kmonsoor Feb 08 '18 at 16:38
2

Note also that group_concat encounters a single NULL value it will omit the entire row that contained it. I work around this in the second caveat [here](https://stackoverflow.com/a/51369193/1054322). – MatrixManAtYrService Jul 16 '18 at 20:12
1

If anyone facing problem with the SQL Fiddle link given in answer. Working Fiddle is here: http://sqlfiddle.com/#!9/42f994/601/0 – Hitesh Aug 06 '18 at 10:49

score 23 · Answer 2 · answered Nov 19 '12 at 10:20

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

score 9 · Answer 3 · edited Jun 13 '15 at 14:33

9

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

edited Jun 13 '15 at 14:33

shA.t

16,580
5
54
111

answered Dec 22 '14 at 13:20

John

541
3
6
19

9

It would be nice if you could add some description to your answer. This is a suggestion to improve this and future answers. Thanks! – Luís Cruz Jun 13 '15 at 17:47

score 6 · Answer 4 · answered Aug 14 '17 at 16:55

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

score 5 · Answer 5 · answered May 17 '17 at 07:04

 SELECT id, GROUP_CONCAT(CONCAT_WS(':', Name, CAST(Value AS CHAR(7))) SEPARATOR ',') AS result 
    FROM test GROUP BY id

you must use cast or convert, otherwise will be return BLOB

result is

id         Column
1          A:4,A:5,B:8
2          C:9

you have to handle result once again by program such as python or java

score 1 · Answer 6 · edited Mar 20 '21 at 08:50

1

SELECT 
    id, 
    Group_concat(`column`) 
FROM
    (SELECT 
        id, 
        Concat(`name`, ':', Group_concat(`value`)) AS `column` 
    FROM mytbl 
    GROUP  BY id, name) tbl 
GROUP BY id;

edited Mar 20 '21 at 08:50

simon.ro

2,984
2
22
36

answered Oct 06 '19 at 18:05

Mrigank Shekhar

67
3

score 0 · Answer 7 · answered Nov 28 '13 at 09:35

IF OBJECT_ID('master..test') is not null Drop table test

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 distinct NAME , LIST = Replace(Replace(Stuff((select ',', +Value from test where name = _a.name for xml path('')), 1,1,''),'<Value>', ''),'</Value>','') from test _a order by 1 desc

My table name is test , and for concatination I use the For XML Path('') syntax. The stuff function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

STUFF functions looks like this : STUFF (character_expression , start , length ,character_expression )

character_expression Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

start Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

length Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.

How to use GROUP_CONCAT in a CONCAT in MySQL

7 Answers7

Linked

Related