sql 拆分行/汇总行

--一套房可能对应多个买方，根据saleguid进行合并
select saleguid,
        stuff((select ','+cstname from  s_buyer  
            where c.saleguid=saleguid 
            for xml path('')),1,1,'') as cstnames   
		from s_buyer c   
		group by c.saleguid 

--一个组团对应了多个科目StageAccountGUIDs，现在拆分成多行，每一行对应一个科目
select a.StageAccountGroupGUID , 
		   a.GroupName ,
		   a.ProjGUID ,
		StageAccountGUID = substring(a.StageAccountGUIDs , b.number , charindex(',' , a.StageAccountGUIDs + ',' , b.number) - b.number) 
	from cb_StageAccountGroup a join master..spt_values  b 
	on b.type='p' and b.number between 1 and len(a.StageAccountGUIDs)
	where substring(',' + a.StageAccountGUIDs , b.number , 1) = ','
		  and ProjGUID in (@ProjGUID)
		  and GroupTypeEnum = 1  --是组团
		  and IsEnable = 1 --已启用