MySQL Query: Values from multiple cells in a single cell

Question

There is a table name agents which contains a list of agents and another table named projects, which contain list of projects and IDs of agent working on that project.

The output I am looking for is a list of agents, a total count of projects they are managing and list of projects.

Sample tables:

agents

AgentID     AgentName
------      ---------
1           Name1
2           Name2
3           Name3

projects

ProjectID    ProjectName   agentID
---------    -----------   -------
1            PName1        2
2            PName2        3
3            PName3        2

Desired output:

AgentName   No. of Projects   ProjectNames
---------   ---------------   ------------
Name2       2                 PName1
                              PName3
Name3       1                 PName2

Using the below query I am able to concatenate all projects in one cell..

SELECT GROUP_CONCAT(projectName SEPARATOR ' , ') AS project_name FROM projects

But how can I concatenate projects linked to 1 agent... (as shown in the desired output table)?

Is there any solution to this problem?

I'll appreciate any help from this community...

Do you require this exact formatting? – Tim Biegeleisen Apr 22 '16 at 10:21 — Tim Biegeleisen, Apr 22 '16 at 10:21
Yes, as i want to show it as a report.. – Ahsan Arshad Apr 22 '16 at 10:24 — Ahsan Arshad, Apr 22 '16 at 10:24

score 2 · Accepted Answer · answered Apr 22 '16 at 10:21

2

SELECT GROUP_CONCAT(projectName SEPARATOR ' , ') AS project_name, a.AgentID
FROM   projects p
       join agents a on (p.AgentID = a.AgentID)
GROUP BY a.AgentID

answered Apr 22 '16 at 10:21

Pavel Zimogorov

1,387
10
24

thanks its working.. but is it possible to get one project in one line? – Ahsan Arshad Apr 22 '16 at 10:38
try to play with separator. It will not affect in workbench, but an application'll move data to next row – Pavel Zimogorov Apr 22 '16 at 10:39

MySQL Query: Values from multiple cells in a single cell

1 Answers1