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

Question

我已经在Google的帮助下编写了此查询，以便从表格中创建定界列表，但是我对此查询一无所知。

谁能解释我发生了什么事

 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

Answer 1

解释它的最简单方法是查看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删除’Employee’会删除外部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 ('')

创造

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标记，并且在select查询中添加逗号意味着任何带有空格的名称都不会引起错误，STUFF将删除第一个逗号和空格。

附录

为了详细说明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;