SQL stuff求集合

--方法一 stuff(xml path(),1,1,'')
select director_su_name      
,stuff(
(
select  ',' + member_su_name   from su_team_all_vice 
where director_su_name = b.director_su_name for xml path('')
), 1, 1, ''
) 
 member_su_name from su_team_all_vice b 
group by director_su_name;


--方法二 xml path
select director_su_name, member_su_name=(
select member_su_name +',' from su_team_all_vice as b where
 b.director_su_name = a.director_su_name
 for xml path('')) from su_team_all_vice as a
group by director_su_name


--方法三 left+ xml path
SELECT B.director_su_name,LEFT(StuList,LEN(StuList)-1) as member_su_name FROM (
SELECT director_su_name,
(SELECT member_su_name+',' FROM su_team_all_vice 
  WHERE director_su_name=A.director_su_name 
  FOR XML PATH('')) AS StuList
FROM su_team_all_vice A 
GROUP BY director_su_name
) B


select director_su_name,member_su_name from su_team_all_vice