sql - 使用 group by 子句获取每个组中的所有记录

Question

我有一张这样的桌子

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

我希望结果是

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

到目前为止我能想到的查询是：

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

但是这个行不通，因为 EmployeeName 列不在Group By子句中，如果我在子句中命名该列，查询将给出错误的结果Group By。

有人可以告诉我存档该结果的方式吗？

标签： sqlsql-server

Answer 1

您可以使用string_agg()：

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

对于旧版本，您可以执行以下操作：

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;