2

I have a table in SQL Server 2008 like this:

Id   Name
------------
1    Jack
2    John
3    Maria
4    Jack
5    Jack
6    John

I am trying to see all the Ids having the same name in one column.

select Count(Id), Name
from myTable
group by Name

The query above is giving me the number of Ids having the same name. But what I would like to see is:

    Id    Name
    ------------
    1,4,5 Jack
    2,6   John
    3     Maria

How can I provide this? Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Emel Uras
  • 394
  • 2
  • 13
  • That's a SQL anti pattern; one field should not contain multiple values. If you explain why you need this, how it will be used, we may be able to provide an alternative pattern / solution. – MatBailie Nov 17 '16 at 14:21
  • @xQbert `ListAgg()` is only in Oracle. – RBarryYoung Nov 17 '16 at 14:26
  • 2
    The MSFT equivelant to `ListAgg()` is the function you're looking for: use `for XML path` & `stuff` http://stackoverflow.com/questions/15477743/listagg-in-sqlserver – xQbert Nov 17 '16 at 14:27
  • @MatBailie The field does not have multiple values. – paparazzo Feb 12 '18 at 17:54
  • @paparazzi - A single string with `'1,4,5'` is a non atomic value. Why the necro comment? – MatBailie Feb 12 '18 at 18:03
  • @MatBailie Not going to play semantics with you. String is a report is not a SQL field. – paparazzo Feb 12 '18 at 18:09

1 Answers1

3
Declare @YourTable table (ID int, Name varchar(50))
Insert Into @YourTable values
(1,'Jack'),
(2,'John'),
(3,'Maria'),
(4,'Jack'),
(5,'Jack'),
(6,'John')

Select Name
      ,IDs  = Stuff((Select Distinct ','+cast(ID as varchar(25))
                     From   @YourTable
                     Where  Name=A.Name
                     For XML Path ('')),1,1,'') 
 From  (Select Distinct Name From @YourTable ) A

Returns

Name    IDs
Jack    1,4,5
John    2,6
Maria   3
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66