How To Use MySQL GROUP_CONCAT Function

3118
Share:
how-to-use-mysql-group-concat-function

What Is It?

MySQL GROUP_CONCAT() function concatenates strings from a group into a single string with various options. This function returns a string with concatenated non-NULL value from a group, and returns NULL when there are no non-NULL values.

Syntax

GROUP_CONCAT(expr);

Where expr is an expression.

Example

Let's say we have first_name column in users table like below:

SELECT `first_name` FROM `ci_users`

And returning rows like below:

Amy
Nicole
Kaitlin
Rita
Gina
Rebecca
Malu
Rachel
Carol
Diane

 

The simplest GROUP_CONCAT query is like below:

SELECT GROUP_CONCAT(`first_name`)  FROM `ci_users`

Will result like this:

Amy,Nicole,Kaitlin,Rita,Gina,Rebecca,Malu,Rachel,Carol,Diane

To use custom character as separator, use this query:

SELECT GROUP_CONCAT(`first_name` SEPARATOR ", ")  FROM `ci_users`

Will result like this:

Amy, Nicole, Kaitlin, Rita, Gina, Rebecca, Malu, Rachel, Carol, Diane

To order first name alphabetically, use this query:

SELECT GROUP_CONCAT(`first_name` ORDER BY `first_name` ASC SEPARATOR ", ") FROM `ci_users` 

Will result like this:

Amy, Carol, Diane, Gina, Kaitlin, Malu, Nicole, Rachel, Rebecca, Rita

 

Tags
Share:

1 comment

  1. Thanks for your personal marvelous posting! I definitely enjoyed reading it, you will be a great author.
    I will remember to bookmark your blog and definitely
    will come back sometime soon. I want to encourage you to
    definitely continue your great job, have a nice afternoon!

Leave a reply

Your email address will not be published. Required fields are marked *