表:

Id Name
1 aaa
1 bbb
1 ccc
1 ddd
1 eee

要求输出:

Id abc
1 aaa,bbb,ccc,ddd,eee

查询:

SELECT ID, 
    abc = STUFF(
                 (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
               ) 
FROM temp1 GROUP BY id

该查询工作正常。但我只是需要解释一下它是如何工作的,或者是否有其他或简单的方法来做到这一点。

我有点搞不懂了。


当前回答

下面是它的工作原理:

1. 使用FOR XML获取XML元素字符串

在查询的末尾添加FOR XML PATH允许您将查询结果作为XML元素输出,元素名称包含在PATH参数中。例如,如果我们要运行以下语句:

SELECT ',' + name 
              FROM temp1
              FOR XML PATH ('')

通过传入一个空字符串(FOR XML PATH(")),我们得到以下结果:

,aaa,bbb,ccc,ddd,eee

2. 用STUFF去掉前导逗号

STUFF语句字面上“塞入”一个字符串到另一个字符串,替换第一个字符串中的字符。但是,我们只是使用它来删除结果值列表的第一个字符。

SELECT abc = STUFF((
            SELECT ',' + NAME
            FROM temp1
            FOR XML PATH('')
            ), 1, 1, '')
FROM temp1

STUFF的参数为:

要“填充”的字符串(在我们的例子中,是完整的名称列表 领先的逗号) 开始删除和插入字符的位置(1,我们正在填充到一个空白字符串中) 要删除的字符数(1,是前导逗号)

所以我们最终得到:

aaa,bbb,ccc,ddd,eee

3.加入id以获得完整列表

接下来,我们只需将其连接到临时表中的id列表中,以获得带有name的id列表:

SELECT ID,  abc = STUFF(
             (SELECT ',' + name 
              FROM temp1 t1
              WHERE t1.id = t2.id
              FOR XML PATH (''))
             , 1, 1, '') from temp1 t2
group by id;

我们得到了结果:

Id Name
1 aaa,bbb,ccc,ddd,eee

其他回答

SELECT ID, 
    abc = STUFF(
                 (SELECT ',' + name FROM temp1 FOR XML PATH ('')), 1, 1, ''
               ) 
FROM temp1 GROUP BY id

在上面的查询中,STUFF函数用于从生成的xml字符串(,aaa,bbb,ccc,ddd,eee)中删除第一个逗号(,),然后它将变成(aaa,bbb,ccc,ddd,eee)。

而FOR XML PATH(")只是将列数据转换为(,aaa,bbb,ccc,ddd,eee)字符串,但在PATH中我们传递的是",因此它不会创建XML标记。

最后,我们使用ID列对记录进行分组。

Azure SQL数据库和SQL Server(从2017年开始)中有非常新的功能来处理这种确切的场景。我相信这可以作为您试图用XML/STUFF方法完成的工作的本地官方方法。例子:

select id, STRING_AGG(name, ',') as abc
from temp1
group by id

STRING_AGG - https://msdn.microsoft.com/en-us/library/mt790580.aspx

编辑:当我最初发布这篇文章时,我提到了SQL Server 2016,因为我认为我在一个潜在的功能上看到了它。要么是我记错了,要么是发生了什么变化,感谢修改版本的建议编辑。此外,我印象非常深刻,并没有完全意识到让我做出最终选择的多步骤审查过程。

本文介绍了在SQL中连接字符串的各种方法,包括对连接的值不进行xml编码的代码的改进版本。

SELECT ID, abc = STUFF
(
    (
        SELECT ',' + name
        FROM temp1 As T2
        -- You only want to combine rows for a single ID here:
        WHERE T2.ID = T1.ID
        ORDER BY name
        FOR XML PATH (''), TYPE
    ).value('.', 'varchar(max)')
, 1, 1, '')
FROM temp1 As T1
GROUP BY id

要理解发生了什么,从内部查询开始:

SELECT ',' + name
FROM temp1 As T2
WHERE T2.ID = 42 -- Pick a random ID from the table
ORDER BY name
FOR XML PATH (''), TYPE

因为您指定了FOR XML,所以您将得到一个包含表示所有行的XML片段的单行。

因为没有为第一列指定列别名,所以每一行都被包装在一个XML元素中,在for XML PATH后面的方括号中指定了名称。例如,如果你有For XML PATH ('X'),你会得到一个看起来像这样的XML文档:

<X>,aaa</X>
<X>,bbb</X>
...

但是,因为你没有指定一个元素名称,你只是得到一个值列表:

,aaa,bbb,...

. value('。', 'varchar(max)')只是从结果XML片段中检索值,而不进行XML编码任何“特殊”字符。你现在有一个看起来像这样的字符串:

',aaa,bbb,...'

然后STUFF函数删除前导逗号,给出的最终结果如下所示:

'aaa,bbb,...'

乍一看,它看起来相当混乱,但与其他一些选项相比,它确实表现得相当好。

下面是它的工作原理:

1. 使用FOR XML获取XML元素字符串

在查询的末尾添加FOR XML PATH允许您将查询结果作为XML元素输出,元素名称包含在PATH参数中。例如,如果我们要运行以下语句:

SELECT ',' + name 
              FROM temp1
              FOR XML PATH ('')

通过传入一个空字符串(FOR XML PATH(")),我们得到以下结果:

,aaa,bbb,ccc,ddd,eee

2. 用STUFF去掉前导逗号

STUFF语句字面上“塞入”一个字符串到另一个字符串,替换第一个字符串中的字符。但是,我们只是使用它来删除结果值列表的第一个字符。

SELECT abc = STUFF((
            SELECT ',' + NAME
            FROM temp1
            FOR XML PATH('')
            ), 1, 1, '')
FROM temp1

STUFF的参数为:

要“填充”的字符串(在我们的例子中,是完整的名称列表 领先的逗号) 开始删除和插入字符的位置(1,我们正在填充到一个空白字符串中) 要删除的字符数(1,是前导逗号)

所以我们最终得到:

aaa,bbb,ccc,ddd,eee

3.加入id以获得完整列表

接下来,我们只需将其连接到临时表中的id列表中,以获得带有name的id列表:

SELECT ID,  abc = STUFF(
             (SELECT ',' + name 
              FROM temp1 t1
              WHERE t1.id = t2.id
              FOR XML PATH (''))
             , 1, 1, '') from temp1 t2
group by id;

我们得到了结果:

Id Name
1 aaa,bbb,ccc,ddd,eee

我做了调试,最终返回我的'填充'查询到它的正常方式。

简单的

select * from myTable for xml path('myTable')

给出了要从我调试的触发器写入日志表的表的内容。