SQL Server- concat几乎没有列值，加入一个列中

Question

连接多个表后，我的结果仅在一列中有所不同。是否有一种"简单"将这些差异压缩到一排的方法？

例如，让我们假设加入我有类似的东西：

id | project | date     | Oranges | Apples | Name
1     xaxa    1.1.2000   yes        yes      Tom
1     xaxa    1.1.2000   yes        yes      Bob
1     xaxa    1.1.2000   yes        yes      Jan

我想拥有这样的东西：

id | project | date      | Oranges | Apples | Name
1    xaxa      1.1.2000    yes       yes      Tom, Bob, Jan

仍然在这里开头，请保持温柔：）

Answer 1

select id, project, date, Oranges, Apples 
  , Names = stuff((
    select distinct ', '+i.Name
      from t as i
      where i.id      = t.id
        and i.project = t.project
        and i.date    = t.date
        and i.Oranges = t.Oranges
        and i.Apples  = t.Apples
      order by i.Name
      for xml path (''), type).value('.','varchar(max)')
    ,1,2,'')
from t
group by id, project, date, Oranges, Apples