不幸的是，SQL Server 没有一个简单的函数来生成逗号分隔的列表，您必须实现FOR XML PATH获取列表。
有几种方法可以做到这一点，您可以使用STUFF and FOR XML PATH:
select distinct t1.country,
t1.ProjectTA,
t1.Complexity,
t1.TID,
t1.Sites,
STUFF(
(SELECT ', ' + t2.Inits
FROM yt t2
where t1.Country = t2.Country
and t1.ProjectTA = t2.ProjectTA
and t1.TID = t2.TID
and t1.Sites = t2.Sites
and t1.name = t2.name
FOR XML PATH (''))
, 1, 1, '') AS inits,
t1.name
from yt t1;
See SQL Fiddle 演示
或者你可以使用CROSS APPLY and FOR XML PATH:
select distinct t1.country,
t1.ProjectTA,
t1.Complexity,
t1.TID,
t1.Sites,
left(t2.inits, len(t2.inits)-1) inits,
t1.name
from yt t1
cross apply
(
select t2.Inits + ', '
from yt t2
where t1.Country = t2.Country
and t1.ProjectTA = t2.ProjectTA
and t1.TID = t2.TID
and t1.Sites = t2.Sites
and t1.name = t2.name
FOR XML PATH('')
) t2 (inits);
See SQL 摆弄演示。这些都产生结果：
| COUNTRY | PROJECTTA | COMPLEXITY | TID | SITES | INITS | NAME |
-------------------------------------------------------------------------------------------
| United States | A8022 | Obesity Low | 4692 | 69 | hale | ePublishing Group |
| United States | A8022 | Obesity Low | 4692 | 69 | jpni, jpni | CBM Budget |
| United States | A8022 | Obesity Low | 4692 | 69 | JT, PIHR | AD |
| United States | S8033 | CNS Low | 5423 | 69 | dedu, AHrp | ePublishing Group |
| United States | S8033 | CNS Medium | 5423 | 69 | dedu, AHrp | ePublishing Group |
| United States | S8033 | CNS Medium | 5423 | 69 | ShyP | CBM Payment |
