1

I have a table in sql server 2008 with format:

Name                                   number
a                                       123
a                                       231
b                                       521
b                                       236

then i want get result :

Name                                    number
a                                        123,231
b                                        521,236
Taryn
  • 234,956
  • 54
  • 359
  • 399
user3250081
  • 11
  • 1

1 Answers1

1

You can do this using concatenate in xml path.

        Create table #t ( Name varchar(1),Number int)
        Insert into #t
        values
        ('a',123),
        ('a',231),
        ('b',521),
        ('b',236)


        Select distinct
        t.name,
        stuff(
            ( Select ','+ convert(varchar(50),number)
            from #t t1
             where t.name =t1.name
             order by name
             for xml path ('')
             )
             ,1,2,'') as number


        from #t t
        order by name
Blindy
  • 60,429
  • 9
  • 84
  • 123
Maverick
  • 1,137
  • 1
  • 8
  • 15
  • 1
    Either the third argument of STUFF should be 1 instead of 2 or the delimiter should be something like `', '` i.e. 2 characters instead of 1. – Andriy M Jan 29 '14 at 21:11