表a中id与表b中aid为一对多的关系
例如:
表a:
id | name |
a1 | tom |
a2 | lily |
a3 | lucy |
表b:
id | aid | value |
b1 | a1 | b1 |
b2 | a1 | b2 |
b3 | a2 | b3 |
b4 | a3 | b4 |
b5 | a2 | b5 |
b6 | a3 | b6 |
b7 | a3 | b7 |
使用for xml path(”) 和stuff合并显示多行数据到一行中 :
第一种,不使用stuff,结果如下:
select id, [val]=( select [value] +',' from tb as b where b.id = a.id for xml path('') ) from tb as a group by id
结果:
id | val |
a1 | b1,b2, |
a2 | b3,b5, |
a3 | b4,b6,b7, |
第二种,使用stuff将最后的逗号去掉
select id, [val]=stuff(( select ','+[value] from tb as b where b.id = a.id for xml path('')),1,1,'') from tb as a group by id
结果:
id | val |
a1 | b1,b2 |
a2 | b3,b5 |
a3 | b4,b6,b7 |