Get all record in each group using group by clause

I have a table like this

DepartmentId    EmployeeName
1               A
1               B
2               C
2               D
2               E

I want the result to be

DepartmentId    EmployeeNames
1               A, B
2               C, D, E

and my query I can think of so far is:

select DepartmentId, (select EmployeeName for json path) as EmployeeNames
from EmployeeTable
group by DepartmentId
for json path

But this one doesn’t work out because EmployeeName column is not in the Group By clause, the query will give the wrong result if I name that column in the Group By clause.

Could someone show me the way I can archive that result?

Answer

You can use use string_agg() :

select DepartmentId, string_agg(EmployeeName, ',')
from EmployeeTable
group by DepartmentId;

For older version, you can do :

select et.DepartmentId,
       stuff((select concat(',', ett.EmployeeName)
              from EmployeeTable ett 
              where et.DepartmentId = ett.DepartmentId
              for xml path('')
              ), 1, 2, ''
            ) 
from (select distinct DepartmentId from EmployeeTable) et;