sql server stuff（）多行合并成一行

原数据查询：

select  t1.FBILLNO,t3.FNUMBER  From T_SAL_ORDER t1 
left join T_SAL_ORDERENTRY t2 on t1.FID=t2.FID 
left join T_BD_MATERIAL t3 on t2.FMATERIALID=t3.FMATERIALID 
 where t3.FNUMBER='1.01'  

原数据结果：

FBILLNO	  	FNUMBER
XSDD000007	1.01
XSDD000023	1.01
XSDD000024	1.01
XSDD000045	1.01
XSDD000050	1.01

使用stuff()查询:

select  
stuff((select  '/'+ t1.FBILLNO From T_SAL_ORDER t1 
left join T_SAL_ORDERENTRY t2 on t1.FID=t2.FID 
left join T_BD_MATERIAL t3m on t2.FMATERIALID=t3m.FMATERIALID 
where t3m.FNUMBER=t3.FNUMBER group by t1.FBILLNO  for xml path('')),1,1,'' )FBILLNO 
,t3.FNUMBER From T_SAL_ORDER t1 
left join T_SAL_ORDERENTRY t2 on t1.FID=t2.FID 
left join T_BD_MATERIAL t3 on t2.FMATERIALID=t3.FMATERIALID where t3.FNUMBER='1.01' group by t3.FNUMBER

使用stuff()查询结果:

FBILLNO													FNUMBER
XSDD000007/XSDD000023/XSDD000024/XSDD000045/XSDD000050	1.01