Multiple rows combined based on a column

dbi · #1 (**permalink**) 09-06-11, 13:32

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

sqlserverdba1 · #2 (**permalink**) 11-20-11, 14:32

Use PIVOT and UNPIVOT feature of T-Sql

Using PIVOT and UNPIVOT

homerow · #3 (**permalink**) 11-24-11, 05:51

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

Internet Services

Web Development

Digital Marketing

Consumer Tech