SQL 集約して列を横展開

[Groups]
 
[GroupMembers]
  
 ↓　①[Groups]と[GroupMembers]
 
  
  ↓　②[GroupMembers]
   

①
SELECT   G.GroupCode,
         G.GroupName,
         REPLACE( (SELECT RTRIM(MemberName) AS [data()]
                   FROM GroupMembers
                   WHERE GroupCode = G.GroupCode
                   ORDER BY MemberName
                   FOR XML PATH ('')), ' ', ',') AS MemberNames
FROM     Groups AS G;

--項目の間にスペースあり
SELECT   G.GroupCode,
         G.GroupName,
         SUBSTRING( (SELECT ',' + RTRIM(MemberName)
                   FROM GroupMembers
                   WHERE GroupCode = G.GroupCode
                   ORDER BY MemberName
                   FOR XML PATH ('')), 2, 100000000) AS MemberNames
FROM     Groups AS G;

②
SELECT   G.GroupCode,
         REPLACE( (SELECT RTRIM(MemberName) AS [data()]
                   FROM GroupMembers
                   WHERE GroupCode = G.GroupCode
                   ORDER BY MemberName
                   FOR XML PATH ('')), ' ', ',') AS MemberNames
FROM     GroupMembers AS G
GROUP BY GroupCode
ORDER BY GroupCode;

参考
カラムの値からカンマ区切り (CSV) の文字列を生成する - SQL Server 入門