如何在SQL中基于员工ID生成对应地区的逗号分隔字符串？

涉及的数据表 #

EmployeeWorkLocations表 #

查询语句：

select * from [dbo].[EmployeeWorkLocations] order by employeeid

对应数据：

WLId    EmployeeId  WLStateId
28      1           2
29      1           1
30      1           3
73      1           3
81      2           1
82      2           2
67      2           2
69      2           3
70      2           2
5       5           1
6       5           2
7       5           3
91      6           1
92      6           2
83      6           1
90      6           2
88      6           2
26      6           3
93      7           1
94      7           2
95      7           4
27      8           3

WorkLocation表 #

查询语句：

select * from [dbo].[WorkLocation]

对应数据：

Id  State
1   Delhi
2   Orissa
3   Mumbai
4   Pune

期望输出 #

按员工ID分组，将每个员工的工作地点按原始记录顺序拼接成逗号分隔的字符串：

EmployeeId  State
1           Orissa, Delhi, Mumbai, Mumbai
2           Delhi, Orissa, Orissa, Mumbai, Orissa
5           Delhi, Orissa, Mumbai
6           Delhi, Orissa, Delhi, Orissa, Orissa, Mumbai
7           Delhi, Orissa, Pune
8           Mumbai

实现方案 #

方案一：SQL Server 2017及更高版本（使用STRING_AGG函数） #

SQL Server 2017及以上版本提供了专门的字符串聚合函数STRING_AGG，写法简洁直观：

SELECT
    ewl.EmployeeId,
    STRING_AGG(wl.State, ', ') WITHIN GROUP (ORDER BY ewl.WLId) AS State
FROM [dbo].[EmployeeWorkLocations] ewl
INNER JOIN [dbo].[WorkLocation] wl ON ewl.WLStateId = wl.Id
GROUP BY ewl.EmployeeId
ORDER BY ewl.EmployeeId;

通过WITHIN GROUP (ORDER BY ewl.WLId)保证拼接顺序和原始表中每个员工的记录顺序一致，匹配期望输出的排序要求。

方案二：SQL Server 2016及更早版本（使用STUFF+FOR XML PATH） #

针对旧版本SQL Server，可通过FOR XML PATH拼接字符串，再用STUFF去除开头多余的分隔符：

SELECT
    DISTINCT ewl.EmployeeId,
    STUFF(
        (SELECT ', ' + wl.State
         FROM [dbo].[EmployeeWorkLocations] ewl2
         INNER JOIN [dbo].[WorkLocation] wl ON ewl2.WLStateId = wl.Id
         WHERE ewl2.EmployeeId = ewl.EmployeeId
         ORDER BY ewl2.WLId
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS State
FROM [dbo].[EmployeeWorkLocations] ewl
ORDER BY ewl.EmployeeId;

子查询中FOR XML PATH('')会把每个员工的工作地点拼接成类似, Orissa, Delhi的字符串，STUFF(..., 1, 2, '')去掉开头的, 得到干净结果，同样通过ORDER BY ewl2.WLId保证顺序正确。

内容的提问来源于stack exchange，提问作者Navin Kumar