How to use GROUP_BY to concatenate strings in MySQL?

sql

group_concat

mysql

string-concatenation

byAlex Kataev·Feb 22, 2025

Employ GROUP_CONCAT() for string aggregation in conjunction with GROUP BY in MySQL.

Example code:

SELECT employee_department, GROUP_CONCAT(employee_name SEPARATOR ', ') 
FROM employees 
GROUP BY employee_department;

This example results in a compiled string per department that consolidates all employee_names, this can streamline your data manipulation strategies.

Insight of GROUP_CONCAT

An Overview of GROUP_CONCAT

MySQL's GROUP_CONCAT() acts as your personal power tool when handling string concatenation among multiple rows into one, based on common identifiers. Imagine having a shopping list neatly categorized by aisles. Sounds handy, right?

Separator Management with GROUP_CONCAT

GROUP_CONCAT() provides a SEPARATOR clause to decide how your strings will be merged. You can choose any delimiter that suits your data like a comma ,, space , or even a line-break \n. Without it, the function would act like a grumpy programmer, who just uses comma , by default.

Here's how it works:

SELECT id, GROUP_CONCAT(name SEPARATOR ' ') 
FROM table 
GROUP BY id;

Special handling of NULL values

Keep in mind, GROUP_CONCAT() may play hide and seek by returning NULL if it can't find any non-NULL rows within a group. Oh, and if there are several values in the mix, it will slyly bypass those NULLs and merge only the non-NULL values.

Structure of GROUP_CONCAT

Understanding the syntax of GROUP_CONCAT() is key to writing more complex queries:

GROUP_CONCAT([DISTINCT] expr [,expr ...]
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]]
[SEPARATOR str_val])

Evading Character Limitations in GROUP_CONCAT

If your CONCAT_GROUP seems like it swallowed a horse tranquilizer and output only 1024 characters, don't panic. The group_concat_max_len system variable can increase the limit. 🎉

Advanced usage of GROUP_CONCAT

Using DISTINCT within GROUP_CONCAT

You can use DISTINCT to ensure that the merge operation returns unique values only and to verify that the function isn't just repeating the last joke it heard.

SELECT employee_department, GROUP_CONCAT(DISTINCT employee_name SEPARATOR ', ') 
FROM employees 
GROUP BY employee_department;

ORDER BY inside GROUP_CONCAT

Trust ORDER BY clause to handle organizing your elements in a tidy line within GROUP_CONCAT(). Neat huh?

SELECT employee_department, 
GROUP_CONCAT(employee_name ORDER BY employee_name SEPARATOR ', ') 
FROM employees 
GROUP BY employee_department;

GET JOINS in with GROUP_CONCAT

When your tables get complex, it's time to bring in some bigger guns. By using GROUP_CONCAT() with JOINs, you'll get a fuller picture of your data as it prepares a more elaborate concatenated string from multiple related tables.

SELECT dept.name, GROUP_CONCAT(emp.name ORDER BY emp.name SEPARATOR ', ') 
FROM departments dept
LEFT JOIN employees emp ON emp.department_id = dept.id
GROUP BY dept.name;