此查询如何创建逗号分隔列表 SQL Server？

我在谷歌的帮助下编写了这个查询，从表中创建一个分隔列表，但我不明白这个查询中的任何内容。

谁能解释一下发生了什么事

 SELECT 
    E1.deptno, 
    allemp = Replace ((SELECT E2.ename AS 'data()' 
                       FROM emp AS e2 
                       WHERE e1.deptno = e2.DEPTNO 
                       FOR xml PATH('')), ' ', ', ') 
 FROM EMP AS e1 
 GROUP BY DEPTNO; 

给我结果

10  CLARK, KING, MILLER
20  SMITH, JONES, SCOTT, ADAMS, FORD
30  ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES

解释它的最简单方法是看看如何FOR XML PATH适用于实际的 XML。想象一张简单的桌子Employee:

EmployeeID      Name
1               John Smith
2               Jane Doe

你可以使用

SELECT  EmployeeID, Name
FROM    emp.Employee
FOR XML PATH ('Employee')

这将创建如下 XML

<Employee>
    <EmployeeID>1</EmployeeID>
    <Name>John Smith</Name>
</Employee>
<Employee>
    <EmployeeID>2</EmployeeID>
    <Name>Jane Doe</Name>
</Employee>

从中删除“员工”PATH删除外部 xml 标签，因此此查询：

SELECT  Name
FROM    Employee
FOR XML PATH ('')

会创造

    <Name>John Smith</Name>
    <Name>Jane Doe</Name>

然后你所做的并不理想，列名“data()”强制出现sql错误，因为它试图创建一个不是合法标签的xml标签，因此会生成以下错误：

列名“Data()”包含 FOR XML 所要求的无效 XML 标识符； '('(0x0028) 是第一个出错的字符。

相关子查询隐藏了此错误，只生成不带标签的 XML：

SELECT  Name AS [Data()]
FROM    Employee
FOR XML PATH ('')

creates

John Smith Jane Doe

然后你用逗号替换空格，相当不言自明......

如果我是你，我会稍微调整一下查询：

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH('')
            ), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO; 

没有列别名意味着不会创建 xml 标签，并且在选择查询中添加逗号意味着任何带有空格的名称都不会导致错误，STUFF将删除第一个逗号和空格。

ADDENDUM

为了详细说明 KM 在评论中所说的内容，因为这似乎得到了更多的关注，转义 XML 字符的正确方法是使用.value如下：

SELECT  E1.deptno, 
        STUFF(( SELECT  ', ' + E2.ename 
                FROM    emp AS e2 
                WHERE   e1.deptno = e2.DEPTNO 
                FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') 
FROM    EMP AS e1 
GROUP BY DEPTNO;