SQL: group employees inside a row

Question

I guess, this is a simple one. Let's say, I have two tables: [employees] and [projects]

SELECT p.project_name, concat(e.first_name, " ", e.last_name) as employee_name
    FROM project p
    INNER JOIN employee e on e.project_id = p.id
    ORDER by p.project_name

With that SELECT I get

JohnJane Project | John Doe 
JohnJane Project | Jane Doe
T Project        | Mr. T

But instead I would like to have the names comma-separated in a row:

 JohnJane Project | John Doe, Jane Doe
  T Project | Mr. T

How can I do that?

Thanks Bernhard

GROUP BY combined with MySQL's GROUP_CONCAT? – jarlh Feb 05 '15 at 06:55 — jarlh, Feb 05 '15 at 06:55

score 3 · Accepted Answer · answered Feb 05 '15 at 06:55

3

Group by the project and use GROUP_CONCAT

SELECT p.project_name, 
       group_concat(concat(e.first_name, " ", e.last_name)) as employee_name
FROM project p
INNER JOIN employee e on e.project_id = p.id
GROUP BY p.project_name
ORDER by p.project_name

answered Feb 05 '15 at 06:55

juergen d

201,996
37
293
362

1

Holy cow. You're damn fast! Thanks, that was it. – Bernie Feb 05 '15 at 06:58

SQL: group employees inside a row

1 Answers1