448

Basically the question is how to get from this:

foo_id   foo_name
1        A
1        B
2        C

to this:

foo_id   foo_name
1        A B
2        C
ustun
  • 6,941
  • 5
  • 44
  • 57
Paweł Hajdan
  • 18,074
  • 9
  • 49
  • 65

6 Answers6

665
SELECT id, GROUP_CONCAT(name SEPARATOR ' ') FROM table GROUP BY id;

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

From the link above, GROUP_CONCAT: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

Player1
  • 2,878
  • 2
  • 26
  • 38
Scott Noyes
  • 6,674
  • 1
  • 17
  • 2
171
SELECT id, GROUP_CONCAT( string SEPARATOR ' ') FROM table GROUP BY id

More details here.

From the link above, GROUP_CONCAT: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

FlameStorm
  • 944
  • 15
  • 20
Graeme Perrow
  • 56,086
  • 21
  • 82
  • 121
  • the resulting column has a [limit](http://stackoverflow.com/a/3785928/1500392) in characters. see [here](http://stackoverflow.com/a/3785928/1500392) and the docs :) – marlo Jan 26 '16 at 09:53
  • isn't it foo_id? – tolga Jul 14 '23 at 10:24
23
SELECT id, GROUP_CONCAT(name SEPARATOR ' ') FROM table GROUP BY id;

:- In MySQL, you can get the concatenated values of expression combinations . To eliminate duplicate values, use the DISTINCT clause. To sort values in the result, use the ORDER BY clause. To sort in reverse order, add the DESC (descending) keyword to the name of the column you are sorting by in the ORDER BY clause. The default is ascending order; this may be specified explicitly using the ASC keyword. The default separator between values in a group is comma (“,”). To specify a separator explicitly, use SEPARATOR followed by the string literal value that should be inserted between group values. To eliminate the separator altogether, specify SEPARATOR ''.

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

OR

mysql> SELECT student_name,
    ->     GROUP_CONCAT(DISTINCT test_score
    ->               ORDER BY test_score DESC SEPARATOR ' ')
    ->     FROM student
    ->     GROUP BY student_name;
ustun
  • 6,941
  • 5
  • 44
  • 57
Exundoz
  • 231
  • 2
  • 3
21

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024 characters, so we first do:

SET group_concat_max_len=100000000;

and then, for example:

SELECT pub_id,GROUP_CONCAT(cate_id SEPARATOR ' ') FROM book_mast GROUP BY pub_id
Waqar Alamgir
  • 9,828
  • 4
  • 30
  • 36
  • 1
    The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a _default value_ of **1024** characters. [docs](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) – marlo Jan 26 '16 at 09:48
  • What is the scope of this `group_concat_max_len` config? Current connection/session, or will it affect other clients? – Frozen Flame Jun 28 '16 at 08:34
  • @FrozenFlame: > If no modifier is present, SET changes the session variable. If the variable has no session value, an error occurs. From https://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html – arminrosu Feb 16 '17 at 12:56
  • 3
    This does not answer the OP's question but merely adds helpful information. This should be a comment, not an answer. – Sean the Bean Dec 07 '17 at 14:49
  • 1
    Thank you for this code snippet, which might provide some limited, immediate help. A [proper explanation](https://meta.stackexchange.com/q/114762/349538) would greatly improve its long-term value by showing why this is a good solution to the problem and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you’ve made. – Donald Duck Dec 13 '17 at 22:39
20
SELECT id, GROUP_CONCAT(CAST(name as CHAR)) FROM table GROUP BY id

Will give you a comma-delimited string

ustun
  • 6,941
  • 5
  • 44
  • 57
Wayne
  • 38,646
  • 4
  • 37
  • 49
12

Great answers. I also had a problem with NULLS and managed to solve it by including a COALESCE inside of the GROUP_CONCAT. Example as follows: 

SELECT id, GROUP_CONCAT(COALESCE(name,'') SEPARATOR ' ') 
FROM table 
GROUP BY id;

Hope this helps someone else

ustun
  • 6,941
  • 5
  • 44
  • 57
Mauricio Alo
  • 359
  • 4
  • 6