CODEWITHSUNDEEP
sql-servert-sql
DJPB
DJPB

Reputation: 5819

SQL Server T-SQL concat aggregate

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

Upvotes: 2

Views: 1740

Answers (2)

Xilmiki
Xilmiki

Reputation: 1502

This solution works with no need of deploy from Visual studio or dll file in server.

Copy-Paste and it Work!

http://groupconcat.codeplex.com/

dbo.GROUP_CONCAT(VALUE )
dbo.GROUP_CONCAT_D(VALUE ), DELIMITER )  
dbo.GROUP_CONCAT_DS(VALUE , DELIMITER , SORT_ORDER )
dbo.GROUP_CONCAT_S(VALUE , SORT_ORDER )

Upvotes: 0

KM.
KM.

Reputation: 103697

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)

Upvotes: 2

Related Questions