SQLServer使用for xml path()来拼接行

在写SQL语句时，有时候需要对表中的行进行拼接，这时候可以使用STUFF()和for xml path()语句：

如有表如下：

id  |   name
----------
1   |   aaa
1   |   bbb
1   |   ccc

你想得到的结果如下：

id  |   result
--------------
1   |   aaa,bbb,ccc

有的在业务系统中通过多次查询拼接也可以得到如上结果，但是多次查询数据库会更麻烦。这里可以使用TSQL的STUFF函数和for xml path()方法。

查询语句如下：

select id, result = stuff((select ','+name 
                            from temp1 t1.id = t2.id
                            for xml path), 1, 1,'')
from temp1 t2
group by id

下面解释下上述语句如何执行的：

1 通过for xml生成XML元素字符

在查询语句后面添加for xml path能够将得到XML元素形式的结果，元素名称由path()参数指定，因此如果传入空字符串，执行如下语句：

select ','+name from temp1 for xml path('')

会得到以下输出：

,aaa,bbb,ccc

2 使用stuff函数移除前面的逗号

stuff的函数声明如下：

STUFF ( character_expression , start , length , replaceWith_expression )  

从字面上看也比较简单，就是指定的字符串替换掉目标字符串：

如以下：

select result = stuff((select ','+name from temp1 for xml path('')), 1,1, '')

输出如下：

aaa,bbb,ccc

3 Join操作

要得到以ID为Key的目标结果，还需与之前的表进行join操作：

select id, result = stuff((select ','+name 
                            from temp1 t1.id = t2.id
                            for xml path), 1, 1,'')
from temp1 t2
group by id

注：之前有用到过这种写法，今天遇到又不记得具体用法了，故温习后写在这里当笔记。

参考：

stackoverflower

MSDN