1

I am using SQL Server 2008. I have a table like below:

ID  Name 
1   a
1   b
2   c
3   d

I want output like below:

ID  Name
1   a,b
2   c
3   d
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Pankaj
  • 9,749
  • 32
  • 139
  • 283

2 Answers2

1
SELECT ID, Names = STUFF((SELECT N',' + Name
  FROM dbo.TheTable AS x
  WHERE x.ID = t.ID
  ORDER BY Name
  FOR XML PATH(''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1,1,'')
FROM dbo.TheTable AS t
GROUP BY ID;

On more modern versions (SQL Server 2017+), this is much tidier and more efficient:

SELECT ID, Names = STRING_AGG(Name, N',')
  FROM dbo.TheTable
  GROUP BY ID;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
1
select id, (select itbl.name+','
            from tblname itbl
            where itbl.id=tbl.id
            for xml path('')) name
from tblname tbl
group by id
Blindy
  • 65,249
  • 10
  • 91
  • 131
  • @Aaron, oops, I was changing table names while typing it, fixed! – Blindy Sep 06 '11 at 17:39
  • This also produces an extra trailing comma. You can eliminate this with `REPLACE` (as in my answer) or put the comma first and use `STUFF((select ',' + itbl.name ...), 1, 1, '');` – Aaron Bertrand Sep 06 '11 at 17:44
  • To be fair your solution clobbers any spaces already existing in `name`, but you don't see me nit-picking... In the grand scheme of things, it's easier to just always remove a trailing comma in your controller than figuring out which comma is actually a space. – Blindy Sep 06 '11 at 17:45
  • 2
    I'm not nit-picking, just pointing out that it's different than the OP's requirements. I even told you how to correct it (and corrected mine). Don't be so defensive. – Aaron Bertrand Sep 06 '11 at 17:47
  • Aaron, Your response is not according to my requirement. Reason - I did not ask for XML... – Pankaj Sep 06 '11 at 18:01
  • @Pankaj it never returned XML. – Aaron Bertrand Jan 23 '22 at 14:07