select *,ActionName= stuff((select ',' + ActionName from Sys_Action_Table where ModuleId = Sys_Module_Table.ModuleId for xml path('')) , 1 , 1 , '') from Sys_Module_Table  

查询结果

with awardT as(select a.personName,d.awardName ,c.activityName,d.typeName            from T_Person a            join T_PersonAwardR b           on a.id=b.personId           join T_Activity c           on b.activityId=c.id           join T_Award d           on d.id=b.awardId           where c.activityName='万人长跑大赛' and d.typeName='优秀主持人奖')select B.awardName,LEFT(awards,LEN(awards)-1) as awards FROM (SELECT awardName,(SELECT personName+',' FROM awardT  WHERE awardName=A.awardName  for xml path('')) as awardsfrom awardT A group by awardName) B SELECT  a.awardnaame ,         STUFF(( SELECT  ',' + table1.personname                 FROM    table1                 WHERE   table1.awardnaame = a.awardnaame               FOR                 XML PATH('')               ), 1, 1, '') AS personname FROM    ( SELECT    awardnaame           FROM      table1           GROUP BY  awardnaame         ) a