Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2011
    Posts
    1

    Unanswered: Multiple rows combined based on a column

    I have a table with two columns refid and name and it has the following values
    1 tom
    1 jim
    2 bob
    1 bob

    I need a resultset that would have the following values
    1 tom, jim, bob
    2 bob

    I have tried couple of things one being:
    DECLARE @namelist VARCHAR(1000)
    SELECT @namelist = COALESCE(@namelist +', ' ,'') + name FROM sales where refid = 1
    SELECT @namelist
    but i am looking for a resultset with a unique refid and all the names comma separated for that refid.

    thanks,
    dbi

  2. #2
    Join Date
    Oct 2011
    Posts
    29
    Use PIVOT and UNPIVOT feature of T-Sql

    Using PIVOT and UNPIVOT

  3. #3
    Join Date
    Sep 2011
    Location
    Greenville, SC USA
    Posts
    34

    RE: Multiple rows combined based on a column

    Code:
    select
      r.refid
     ,stuff((select ', ' + name from sales where refid = r.refid for xml path('')),1,2,'') as namelist
    from (select distinct refid from sales) r

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •