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.

mahmud ahsan

Computer programmer and hobbyist photographer from Bangladesh, lives in Malaysia. My [Business | Twitter | Linkedin | Instagram | Flickr | 500px]

You may also like

4 Comments

  • Parveen Sharma
    October 20, 2008 at 5:50 am

    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

  • Parveen Sharma
    October 20, 2008 at 6:15 am

    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

  • scvinodkumar
    August 12, 2009 at 1:33 pm

    suppose if want to list the username based on country, how to list then?

    for example, there is 100 users from different countries, i want to give priority to based on the country viewing the site.

    If i am viewing the site in India, then india user will list first and followed by other users.

  • Bhavana
    September 27, 2013 at 4:13 pm

    thanks for this nice info

LEAVE A COMMENT