Select Data from 3 tables whereas multiple values should be comma separated

Question

I have following 3 tables:

.......
....... ………………………… ……………………………… …………………………

I run the following query

SELECT Employee.EmployeeId,
  EmployeeName,
  ProjectName
FROM Employee
JOIN ProjEmp
ON Employee.EmployeeId=ProjEmp.EmployeeId
JOIN Project
ON Project.ProjectId=ProjEmp.ProjectId

And it gives following result:

But I need result like this:

Suggest me the best query for my desired result.

Answer 1

You can do the following to get what you are looking for :

WITH CTE AS(SELECT Employee.EmployeeId,
  EmployeeName,
  ProjectName
FROM Employee
JOIN ProjEmp
ON Employee.EmployeeId=ProjEmp.EmployeeId
JOIN Project
ON Project.ProjectId=ProjEmp.ProjectId)

SELECT EmployeeId,EmployeeName,
      ProjectName = STUFF((
          SELECT ',' + convert(varchar(10),T2.ProjectName)
          FROM CTE T2
          WHERE T1.EmployeeName = T2.EmployeeName
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM CTE T1
GROUP BY EmployeeId,EmployeeName
ORDER BY EmployeeId

Result:

EMPLOYEEID   EMPLOYEENAME   PROJECTNAME
1            Emp1           ProjA,ProjB
3            Emp3           ProjC
4            Emp4           ProjC,ProjD
5            Emp5           ProjE
7            Emp7           ProjE
8            Emp8           ProjE

See result in SQL Fiddle.