SQL--For Xml Path（聚合多行，转Json）

for xml path是将结果集以XML的格式返回；

用的比较多的地方，就是group by之后，拼接字符串返回

举个例子，看一下for xml path的神奇之处；

create table subjects(name varchar(20),subject varchar(20))
insert into subjects
select '小红' ,'高数' 
union all 
select  '小红','音乐'
union all 
select  '小明','体育'
union all 
select  '小明','武术'
union all 
select  '小明','化学'
union all 
select  '小兰','生物'

创建一张表subjects，有三个人，小红，小明和小兰，分别选修了不同的科目；

执行以下语句，查看得出的结果有什么不同。会发现：

--path后小括号跟的是行与行之间的分隔符

--列如果没有拼接，则列名就是列之间的分隔符

--path后小括号跟的是行与行之间的分隔符
--列如果没有拼接，则列名就是列之间的分隔符
select subject from subjects for xml path;
select subject as sb from subjects for xml path('aaa');
select ','+subject from subjects for xml path('');

所以，写select 语句返回拼接字符串的时候，只需要将path后跟（‘’），select后添加列分割符号，如单引号，分号等就可以了；

select s.name,( select ','+b.subject from subjects as b where b.name=s.name for xml path(''))
from subjects as s
--group by name

 

select ','+b.subject 或者select b.subject+','

可以看出subjects中有多余的分隔符，可在前可在后。所以，去掉分隔符可以使用left,right,stuff函数。

--使用stuff去除首位分隔符
select s.name,stuff(( select ','+b.subject from subjects as b where b.name=s.name for xml path('')),1,1,'') as subjects
from subjects as s
group by name

--使用left函数截取字符串
select name ,right(subjects,len(subjects)-1) as subjects from (
select s.name,( select ','+b.subject from subjects as b where b.name=s.name for xml path('')) as subjects
from subjects as s
group by name)as a

得出的结果一样

延伸：STUFF结合FOR XML PATH可以用来拼接JSON字符串，

select '['+ stuff((select ',{"name": "' + name + '","subjects": "' + subject + '"}'
from subjects for xml path('')),1,1,'') +']'

结果如下：

[{"name": "小红","subjects": "高数"},
{"name": "小红","subjects": "音乐"},
{"name": "小明","subjects": "体育"},
{"name": "小明","subjects": "武术"},
{"name": "小明","subjects": "化学"},
{"name": "小兰","subjects": "生物"}]

我的知乎原文链接