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