SQL server - concat few column values in to one column on join

Question

After joining multiple tables I have some results that differ only in one column. Is there a "easy" way to compact those differences to one row ?

For example, lets assume that after join I have something like this:

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

And I would like to have something like this:

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

Still begginer here, please be gentle :)

Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Sean Lange, Feb 22 '17 at 19:32

score 0 · Answer 1 · answered Feb 22 '17 at 19:34

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

SQL server - concat few column values in to one column on join

1 Answers1