Can I concatenate multiple MySQL rows into one field

Question

Using MySQL  I can do something like   SELECT hobbies FROM peoples hobbies WHERE person id   5    My Output   shopping fishing coding   but instead I just want 1 row  1 col   Expected Output   shopping  fishing  coding   The reason is that I m selecting multiple values from multiple tables  and after all the joins I ve got a lot more rows than I d like   I ve looked for a function on MySQL Doc and it doesn t look like the CONCAT or CONCAT WS functions accept result sets   So does anyone here know how to do this

User · Answer

Alternate syntax to concatenate multiple  individual rows WARNING  This post will make you hungry  Given  I found myself wanting to select multiple  individual rows   instead of a group   and concatenate on a certain field  Let s say you have a table of product ids and their names and prices   ------------ -------------------- -------    product id   name                 price    ------------ -------------------- -------            13   Double Double            5             14   Neapolitan Shake         2             15   Animal Style Fries       3             16   Root Beer                2             17   Lame T-Shirt            15    ------------ -------------------- -------   Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes  Your hungry-hippo user selects 13  15  16  No dessert for her today    Find  A way to summarize your user s order in one line  with pure mysql  Solution  Use GROUP CONCAT with the the IN clause  mysql gt  SELECT GROUP CONCAT name SEPARATOR        AS order summary FROM product WHERE product id IN  13  15  16    Which outputs   ------------------------------------------------    order summary                                     ------------------------------------------------    Double Double   Animal Style Fries   Root Beer    ------------------------------------------------   Bonus Solution  If you want the total price too  toss in SUM    mysql gt  SELECT GROUP CONCAT name SEPARATOR        AS order summary  SUM price  AS total FROM product WHERE product id IN  13  15  16    ------------------------------------------------ -------    order summary                                    total    ------------------------------------------------ -------    Double Double   Animal Style Fries   Root Beer      10    ------------------------------------------------ -------

User · Answer

There s a GROUP Aggregate function  GROUP CONCAT

User · Answer

Use MySQL 5 6 13  session variable and assignment operator like the following  SELECT  logmsg    CONCAT ws      logmsg items  FROM temp SplitFields a    then you can get   test1 test11

User · Answer

Try this  DECLARE  Hobbies NVARCHAR 200         SELECT  Hobbies    Hobbies   hobbies       FROM peoples hobbies WHERE person id   5   TL DR  set  sql     set  result     set  separator   union  r n   SELECT   sql  concat  select     INFORMATION SCHEMA COLUMNS COLUMN NAME       as col name    INFORMATION SCHEMA COLUMNS CHARACTER MAXIMUM LENGTH    as def len       MAX CHAR LENGTH   INFORMATION SCHEMA COLUMNS COLUMN NAME      as  max char len     FROM    INFORMATION SCHEMA COLUMNS TABLE NAME   as sql piece  if  result  if  result     sql concat  result  separator  sql          as dummy FROM INFORMATION SCHEMA COLUMNS  WHERE  INFORMATION SCHEMA COLUMNS DATA TYPE like   char   and INFORMATION SCHEMA COLUMNS TABLE SCHEMA  xxx   and INFORMATION SCHEMA COLUMNS TABLE NAME  yyy   select  result

User · Answer

Use MySQL 5 6 13  session variable and assignment operator like the following  SELECT  logmsg    CONCAT ws      logmsg items  FROM temp SplitFields a    then you can get   test1 test11

User · Answer

Alternate syntax to concatenate multiple  individual rows WARNING  This post will make you hungry  Given  I found myself wanting to select multiple  individual rows   instead of a group   and concatenate on a certain field  Let s say you have a table of product ids and their names and prices   ------------ -------------------- -------    product id   name                 price    ------------ -------------------- -------            13   Double Double            5             14   Neapolitan Shake         2             15   Animal Style Fries       3             16   Root Beer                2             17   Lame T-Shirt            15    ------------ -------------------- -------   Then you have some fancy-schmancy ajax that lists these puppies off as checkboxes  Your hungry-hippo user selects 13  15  16  No dessert for her today    Find  A way to summarize your user s order in one line  with pure mysql  Solution  Use GROUP CONCAT with the the IN clause  mysql gt  SELECT GROUP CONCAT name SEPARATOR        AS order summary FROM product WHERE product id IN  13  15  16    Which outputs   ------------------------------------------------    order summary                                     ------------------------------------------------    Double Double   Animal Style Fries   Root Beer    ------------------------------------------------   Bonus Solution  If you want the total price too  toss in SUM    mysql gt  SELECT GROUP CONCAT name SEPARATOR        AS order summary  SUM price  AS total FROM product WHERE product id IN  13  15  16    ------------------------------------------------ -------    order summary                                    total    ------------------------------------------------ -------    Double Double   Animal Style Fries   Root Beer      10    ------------------------------------------------ -------

User · Answer

You can use GROUP CONCAT  SELECT person id     GROUP CONCAT hobbies SEPARATOR       FROM peoples hobbies GROUP BY person id   As Ludwig stated in his comment  you can add the DISTINCT operator to avoid duplicates  SELECT person id     GROUP CONCAT DISTINCT hobbies SEPARATOR       FROM peoples hobbies GROUP BY person id   As Jan stated in their comment  you can also sort the values before imploding it using ORDER BY  SELECT person id          GROUP CONCAT hobbies ORDER BY hobbies ASC SEPARATOR       FROM peoples hobbies GROUP BY person id   As Dag stated in his comment  there is a 1024 byte limit on the result  To solve this  run this query before your query  SET group concat max len   2048   Of course  you can change 2048 according to your needs  To calculate and assign the value  SET group concat max len   CAST                        SELECT SUM LENGTH hobbies     COUNT      LENGTH                                  FROM peoples hobbies                            GROUP BY person id  AS UNSIGNED

User · Answer

I had a more complicated query  and found that I had to use GROUP CONCAT in an outer query to get it to work   Original Query   SELECT DISTINCT userID  FROM event GROUP BY userID  HAVING count distinct cohort   2     Imploded   SELECT GROUP CONCAT sub userID SEPARATOR        FROM  SELECT DISTINCT userID FROM event  GROUP BY userID HAVING count distinct cohort   2  as sub    Hope this might help someone

User · Answer

For somebody looking here how to use GROUP CONCAT with subquery - posting this example  SELECT i     SELECT GROUP CONCAT userid  FROM favourites f WHERE f itemid   i id  AS idlist FROM items i WHERE i id    someid   So GROUP CONCAT must be used inside the subquery  not wrapping it

User · Answer

There s a GROUP Aggregate function  GROUP CONCAT

User · Answer

Have a look at GROUP CONCAT if your MySQL version  4 1  supports it  See the documentation for more details   It would look something like     SELECT GROUP CONCAT hobbies SEPARATOR          FROM peoples hobbies    WHERE person id   5    GROUP BY  all

User · Answer

In my case I had a row of Ids  and it was neccessary to cast it to char  otherwise  the result was encoded into binary format    SELECT CAST GROUP CONCAT field SEPARATOR      AS CHAR  FROM table

User · Answer

Try this  DECLARE  Hobbies NVARCHAR 200         SELECT  Hobbies    Hobbies   hobbies       FROM peoples hobbies WHERE person id   5   TL DR  set  sql     set  result     set  separator   union  r n   SELECT   sql  concat  select     INFORMATION SCHEMA COLUMNS COLUMN NAME       as col name    INFORMATION SCHEMA COLUMNS CHARACTER MAXIMUM LENGTH    as def len       MAX CHAR LENGTH   INFORMATION SCHEMA COLUMNS COLUMN NAME      as  max char len     FROM    INFORMATION SCHEMA COLUMNS TABLE NAME   as sql piece  if  result  if  result     sql concat  result  separator  sql          as dummy FROM INFORMATION SCHEMA COLUMNS  WHERE  INFORMATION SCHEMA COLUMNS DATA TYPE like   char   and INFORMATION SCHEMA COLUMNS TABLE SCHEMA  xxx   and INFORMATION SCHEMA COLUMNS TABLE NAME  yyy   select  result

User · Answer

There s a GROUP Aggregate function  GROUP CONCAT

User · Answer

we have two way to concatenate columns in MySql  select concat hobbies  as  Hobbies  from people hobbies where 1   Or  select group concat hobbies  as  Hobbies  from people hobbies where 1

User · Answer

In my case I had a row of Ids  and it was neccessary to cast it to char  otherwise  the result was encoded into binary format    SELECT CAST GROUP CONCAT field SEPARATOR      AS CHAR  FROM table

User · Answer

You can change the max length of the GROUP CONCAT value by setting the group concat max len parameter   See details in the MySQL documantation

User · Answer

I had a more complicated query  and found that I had to use GROUP CONCAT in an outer query to get it to work   Original Query   SELECT DISTINCT userID  FROM event GROUP BY userID  HAVING count distinct cohort   2     Imploded   SELECT GROUP CONCAT sub userID SEPARATOR        FROM  SELECT DISTINCT userID FROM event  GROUP BY userID HAVING count distinct cohort   2  as sub    Hope this might help someone

User · Answer

we have two way to concatenate columns in MySql  select concat hobbies  as  Hobbies  from people hobbies where 1   Or  select group concat hobbies  as  Hobbies  from people hobbies where 1

User · Answer

For somebody looking here how to use GROUP CONCAT with subquery - posting this example  SELECT i     SELECT GROUP CONCAT userid  FROM favourites f WHERE f itemid   i id  AS idlist FROM items i WHERE i id    someid   So GROUP CONCAT must be used inside the subquery  not wrapping it

User · Answer

You can change the max length of the GROUP CONCAT value by setting the group concat max len parameter   See details in the MySQL documantation

User · Answer

You can use GROUP CONCAT  SELECT person id     GROUP CONCAT hobbies SEPARATOR       FROM peoples hobbies GROUP BY person id   As Ludwig stated in his comment  you can add the DISTINCT operator to avoid duplicates  SELECT person id     GROUP CONCAT DISTINCT hobbies SEPARATOR       FROM peoples hobbies GROUP BY person id   As Jan stated in their comment  you can also sort the values before imploding it using ORDER BY  SELECT person id          GROUP CONCAT hobbies ORDER BY hobbies ASC SEPARATOR       FROM peoples hobbies GROUP BY person id   As Dag stated in his comment  there is a 1024 byte limit on the result  To solve this  run this query before your query  SET group concat max len   2048   Of course  you can change 2048 according to your needs  To calculate and assign the value  SET group concat max len   CAST                        SELECT SUM LENGTH hobbies     COUNT      LENGTH                                  FROM peoples hobbies                            GROUP BY person id  AS UNSIGNED

User · Answer

Have a look at GROUP CONCAT if your MySQL version  4 1  supports it  See the documentation for more details   It would look something like     SELECT GROUP CONCAT hobbies SEPARATOR          FROM peoples hobbies    WHERE person id   5    GROUP BY  all

User · Answer

Have a look at GROUP CONCAT if your MySQL version  4 1  supports it  See the documentation for more details   It would look something like     SELECT GROUP CONCAT hobbies SEPARATOR          FROM peoples hobbies    WHERE person id   5    GROUP BY  all

User · Answer

Have a look at GROUP CONCAT if your MySQL version  4 1  supports it  See the documentation for more details   It would look something like     SELECT GROUP CONCAT hobbies SEPARATOR          FROM peoples hobbies    WHERE person id   5    GROUP BY  all

User · Answer

You can use GROUP CONCAT  SELECT person id     GROUP CONCAT hobbies SEPARATOR       FROM peoples hobbies GROUP BY person id   As Ludwig stated in his comment  you can add the DISTINCT operator to avoid duplicates  SELECT person id     GROUP CONCAT DISTINCT hobbies SEPARATOR       FROM peoples hobbies GROUP BY person id   As Jan stated in their comment  you can also sort the values before imploding it using ORDER BY  SELECT person id          GROUP CONCAT hobbies ORDER BY hobbies ASC SEPARATOR       FROM peoples hobbies GROUP BY person id   As Dag stated in his comment  there is a 1024 byte limit on the result  To solve this  run this query before your query  SET group concat max len   2048   Of course  you can change 2048 according to your needs  To calculate and assign the value  SET group concat max len   CAST                        SELECT SUM LENGTH hobbies     COUNT      LENGTH                                  FROM peoples hobbies                            GROUP BY person id  AS UNSIGNED

User · Answer

You can use GROUP CONCAT  SELECT person id     GROUP CONCAT hobbies SEPARATOR       FROM peoples hobbies GROUP BY person id   As Ludwig stated in his comment  you can add the DISTINCT operator to avoid duplicates  SELECT person id     GROUP CONCAT DISTINCT hobbies SEPARATOR       FROM peoples hobbies GROUP BY person id   As Jan stated in their comment  you can also sort the values before imploding it using ORDER BY  SELECT person id          GROUP CONCAT hobbies ORDER BY hobbies ASC SEPARATOR       FROM peoples hobbies GROUP BY person id   As Dag stated in his comment  there is a 1024 byte limit on the result  To solve this  run this query before your query  SET group concat max len   2048   Of course  you can change 2048 according to your needs  To calculate and assign the value  SET group concat max len   CAST                        SELECT SUM LENGTH hobbies     COUNT      LENGTH                                  FROM peoples hobbies                            GROUP BY person id  AS UNSIGNED

User · Answer

There s a GROUP Aggregate function  GROUP CONCAT

[mysql] Can I concatenate multiple MySQL rows into one field?

Alternate syntax to concatenate multiple, individual rows

Given:

Find:

Solution:

Bonus Solution:

Examples related to mysql

Examples related to sql

Examples related to concat

Examples related to group-concat