SELECT name , value = ( STUFF(( SELECT ',' + value FROM A WHERE name = Test.name FOR XML PATH('') ), 1, 1, '') ) FROM A AS Test GROUP BY name;
PS:STUFF语句就是为了去掉第一个【逗号】
附STUFF用法:(从原字符的第二个开始共三个字符替换为后面的字符)
表A可以是连表
SELECT cu_MainId ,
value = (SELECT ',' +uRealName
FROM 表A AS a1
INNER JOIN 表B AS a2 on a1.UserId=a2.uId
WHERE MainId= Test.MainId
FOR
XML PATH(''))
FROM HRS_ClassUser AS Test
GROUP BY MainId;