SQL Server T-SQL concat aggregate

Question

hi there

Imagine I have this table

BirthDay     |Name
1-10-2010    | 'Joe'
2-10-2010    | 'Bob' 
2-10-2010    | 'Alice'

How can I get a result like this

BirthDay     |Name
1-10-2010    | 'Joe'
2-10-2010    | 'Bob', 'Alice 

tks

Answer 1

try this:

set nocount on;
declare @t table (BirthDay datetime, name varchar(20))
insert into @t VALUES ('1-10-2010',     'Joe'  )
insert into @t VALUES ('2-10-2010',     'Bob'  )
insert into @t VALUES ('2-10-2010',     'Alice')
set nocount off

SELECT p1.BirthDay,
          stuff(
                   (SELECT
                        ', ' + p2.name  --use this if you want quotes around the names:  ', ''' + p2.name+''''
                        FROM @t p2
                        WHERE p2.BirthDay=p1.BirthDay
                        ORDER BY p2.name
                        FOR XML PATH('') 
                   )
                   ,1,2, ''
               ) AS Names
      FROM @t p1
     GROUP BY 
        BirthDay

OUTPUT:

BirthDay                Names
----------------------- ------------
2010-01-10 00:00:00.000 Joe
2010-02-10 00:00:00.000 Alice, Bob

(2 row(s) affected)