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
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!