一条很用的MSSQL语句
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 awards from 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