How to use GROUP BY to concatenate strings in MySQL

Question

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

User · Answer

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

User · Answer

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 gt  SELECT student name      - gt      GROUP CONCAT DISTINCT test score     - gt                ORDER BY test score DESC SEPARATOR          - gt      FROM student     - gt      GROUP BY student name

User · Answer

SELECT id  GROUP CONCAT CAST name as CHAR   FROM table GROUP BY id   Will give you a comma-delimited string

User · Answer

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

User · Answer

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

User · Answer

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

[sql] How to use GROUP BY to concatenate strings in MySQL?

Examples related to sql

Examples related to mysql

Examples related to string

Examples related to aggregate

Examples related to concatenation