表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 |