MySQL GROUP_CONCAT function

The not so well-known MySQL GROUP_CONCAT function is used... (wait for it)
...to concatenate into a single string multiple results from a grouped query.
Hence the name of this function;

Now, when is it useful ?

Let's say you have a database with employees and departments (you can get this database here : https://launchpad.net/test-db).

Let's say you want to check the list of employees for each department. Here is what you could do :

SELECT departments.dept_name AS Department, GROUP_CONCAT( CONCAT( last_name,  ' ', first_name ) ) AS name
FROM  employees
INNER JOIN dept_emp ON dept_emp.emp_no = employees.emp_no
INNER JOIN departments ON departments.dept_no = dept_emp.dept_no
GROUP BY departments.dept_name;

The result is the following (truncated for the sake of readability) :

Department | name
Customer Service | Sluis Mary,Lortz Huan,Tramer Basil,Billingsley ...
Development | Facello Georgi,Preusig Anneke,Kalloufi Saniya,...
Finance | Stamatiou Magy,Dredge Yinghua,McAlpine Alejandro,...
and so on...

As you can see, this function enables you to use the GROUP BY clause without losing the values associated (the name of the employees in our example).

More tips :
- you can eliminate duplicates in the concatenated values by using the DISTINCT clause

SELECT departments.dept_name AS Department, GROUP_CONCAT(DISTINCT  CONCAT( last_name,  ' ', first_name) ) AS name

• you can change the default separator to whatever symbol you want

  SELECT departments.deptname AS Department, GROUPCONCAT( CONCAT( lastname, ' ', firstname) SEPARATOR '#') AS name

• the result is truncated according to the groupconcatmax_len MySQL variable, but you can change it to the value of your choice

  SET SESSION groupconcatmax_len = 1000000