MySQL – The GROUP_CONCAT() function

GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.

For example if you query:

mysql> SELECT Language FROM CountryLanguage WHERE CountryCode = 'THA';

It outputs:

Language
Chinese
Khmer
Kuy
Lao

To concatenate the values into a single string, you query:

mysql> SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';

Then the output will be:

Languages
Chinese, Khmer, Kuy, Lao

You can also use some format of GROUP_CONCAT(). Like

  • SELECT GROUP_CONCAT( Language SEPARATOR ‘-’ )… It will use ‘-’ instead of ‘,’
  • SELECT GROUP_CONCAT( Language ORDER BY Language DESC )… To change the order and shorting output

One thing to remember: GROUP_CONCAT() ignores NULL values.

About these ads

35 Responses to “MySQL – The GROUP_CONCAT() function”

  1. Parveen Sharma Says:

    update admin_messages
    set subject=’function’
    where messageId = (select max(messageId) from admin_messages)

    this query is create error plz. solve this query solution

    regards

    Parveen Sharma

    • sharmi Says:

      It is not possible to update the same table and use that in the from clause in your subquery. It could be rewritten using joins

  2. Parveen Sharma Says:

    possible more than row result in this format example

    name marks
    abc 36
    xyz 56
    def 76
    abc 86
    abc 66
    def 76

    Result is
    abc xyz def
    36 56 76
    86 76
    66

    regards

    Parveen Sharma

    • Don M Says:

      Did you ever get a query to accomplish this? I’m looking to do something quite similar and am unsure how to do it.

  3. sajjadul robin Says:

    I think group_concat() has actually different purpose:
    e.g. To group or show the people who lives in same address, or show the students who have same score.

    query : select student, group_concat(score) from student group by student_name;

    and if you want to group which has more than one people, the query can be:
    select student, group_concat(score) from student group by student_name having count(student_name) > 1;

    I need this email for one of my projects.

    Happy to share here :)

  4. James Says:

    Thanks for the write up, very helpful.

  5. oasisfleeting Says:

    Thanks a ton!! This is exactly what I was looking for!

  6. andrew Says:

    Thanks man

  7. Daniel Z. Says:

    This was useful, thanks! In addition to the above examples, you can use the DISTINCT keyword to get only unique values within the GROUP_CONCAT. e.g. GROUP_CONCAT(DISTINCT Languages)

  8. Constant Marcove Says:

    thanks much this is most enlightening Parveen,

    is there a function that does the reverse of group_concat
    ie. from languages: english,french,chinese
    to generate separate rows as follows:
    english
    french
    chinese

  9. Constant Marcove Says:

    reverse group_concat in order to resolve the following:

    table1 project=A lang=chinese,french,english,spanish
    table2 lang texte
    ==== ====
    spanish ola
    french bonjour
    german hallo

    i need to do a join of the 2 tables to obtain only the records of table2 based on the languages in table1 from record project=A which has lang=chinese,french,english

    such that the result would be
    lang texte
    ==== ====
    french bonjour
    spanish ola

    thanks much,

  10. regx Says:

    Wonderfully useful.
    I just used this to create a simple function for looking up users based on profile information in Drupal 6.
    see: http://regx.dgswa.com/html/content/search-drupal-user-profiles

  11. double contract beds Says:

    I had been really glad to learn your internet site, it features specifically the content I’m planning to locate.

  12. asdas Says:

    Thanks useful information

  13. Synthetic Wigs Says:

    False Hairpiece http://synthetic-wigs.dolabuy.com/ Com their wide selection of make Synthetic Flowing hair Hair pieces, Particularly long Imitation Wigs, Reasonably priced Fake Wigs, Fast Fabricated Wigs, Internal Fabricated Hairpiece and

  14. saefullohrifai Says:

    keren. terima kasih..

  15. Rohidas Says:

    Grete Post very Helpful function

  16. Arpit Says:

    Thanks, Very easy to understand via example.

  17. Arif Says:

    Thanks Qiu…
    very usefull…

  18. dirtyoilsands.org Says:

    After exploring a number of the blog posts on your website, I seriously like your technique of
    writing a blog. I saved it to my bookmark webpage list and will be checking back soon.
    Please visit my website too and tell me what you think.

  19. yahoo.com Says:

    I really desired to discuss this particular post,
    Roman Shades “MySQL – The GROUP_CONCAT() function | Think Different” together with my own pals on facebook itself.

    Ijust simply planned to disperse ur terrific writing!

    Many thanks, Geneva

  20. binary options trading systems Says:

    Someone necessarily assist to make critically articles I would state.
    This is the first time I frequented your website page and to this point?
    I amazed with the analysis you made to create this actual
    post incredible. Wonderful job!

  21. accounting for stock options issued to non employees Says:

    I’m very happy to find this page. I need to to thank you for your time due to this wonderful read!! I definitely enjoyed every little bit of it and I have you bookmarked to see new things on your site.

  22. orquesta Says:

    Great post. I was checking constantly this weblog and I am impressed!
    Very helpful info specially the final phase :) I take care of such information much.
    I used to be seeking this certain info for a very long time.
    Thank you and good luck.

  23. Leandra Says:

    “MySQL – The GROUP_CONCAT() function | Think Different” was a superb
    blog post, can’t help but wait to read through more of ur posts.
    Time to waste numerous time on-line lol. Thanks for your effort ,Victorina

  24. Elinor Says:

    This particular blog, “MySQL – The GROUP_CONCAT() function |
    Think Different” was in fact outstanding.
    I am printing out a duplicate to demonstrate to my close friends.
    Thanks for the post-Reyes

  25. Sitthykun Says:

    Greate!!

  26. http://ancientswordlegends.wordpress.com/2013/03/16/manhua-1 Says:

    Excellent post. I was checking continuously this weblog and I’m impressed! Very helpful information particularly the ultimate section :) I care for such info much. I used to be looking for this certain information for a very lengthy time. Thank you and best of luck.

  27. Salah Deen Says:

    How can I add a hyperlink to the comma separated values? Imagine I use as the separator can i use those as individual hyperlinks?

  28. convert-db.com Says:

    Hi there very nice site!! Man .. Excellent ..

    Wonderful .. I’ll bookmark your web site and
    take the feeds also? I’m satisfied to find a lot of useful information here in the post, we’d like develop more strategies
    on this regard, thanks for sharing. . . .
    . .

  29. samkam Says:

    nice

  30. samkam Says:

    test


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: