0

I have three tables. I have connected it using a query, but I would like to separate results with comma in one line instead of showing result on different line.

1st table

 ProgrammeId | Name
====================
1 |Software Engineering                                         
2 | Game Design

2nd table

 CoordinatorId| CoordinatorName
====================
1 |Bob                                         
2 | Ted

3d table

 ProgrammeId| CoordinatorId
====================
1 | 1                                         
1 | 2
2 | 1

That is a query.

 SELECT a.ProgrammeId, a.Name, c.NameCoordinator
FROM Programme a
--Joining maptable for Programme and Coordinator
INNER JOIN ProgrammeCoordinators b
ON a.ProgrammeId = b.ProgrammeId
INNER JOIN Coordinator c
ON b.CoordinatorId = c.CoordinatorId

Output will be something like that:

 ProgrammeId| Name | CoordinatorName
==================================
1 | Software Engineer | Bob                                        
1 | Software Engineer  | Ted
2 | Game Design | Ted

How can I separate to show this:

1 | Software Engineer | Bob, Ted
Cœur
  • 37,241
  • 25
  • 195
  • 267
Antoshjke
  • 462
  • 4
  • 22
  • do you really need a comma? wouldn't an xml document do better, like this: `BobTed`? – Quassnoi Apr 14 '16 at 18:56
  • this is often referred to another way, why doesnt sql server have a group_concat like mysql. well good news. that question has been asked already and answered http://stackoverflow.com/a/5981860/1158842. perhaps a better example for you is http://stackoverflow.com/a/17591536/1158842 – hubson bropa Apr 14 '16 at 18:58

1 Answers1

0

If your Name is length 100 :

SELECT a.ProgrammeId, a.Name(Name,' ',100), c.NameCoordinator
FROM Programme a
--Joining maptable for Programme and Coordinator
INNER JOIN ProgrammeCoordinators b
ON a.ProgrammeId = b.ProgrammeId
INNER JOIN Coordinator c
ON b.CoordinatorId = c.CoordinatorId
MackTank
  • 137
  • 9