--方法一 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
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