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

标签: TSQL, STUFF, for xml path, SQLSERVER

添加新评论