sql server - Concatenate a single column into a comma delimited list -

this question has answer here:

• concatenate many rows single text string? 38 answers

i've seen multiple examples of people rolling single column comma delimited list, need bit more.

here example of data , results need.

declare @salesperson table (salespersonid int, salespersonname varchar(10)) declare @region table (regionid int, regionname varchar(15)) declare @salespersonregion table (salespersonid int, regionid int)  insert @salesperson (salespersonid, salespersonname) values (1,'jeff')  insert @salesperson (salespersonid, salespersonname) values (2,'pat')  insert @salesperson (salespersonid, salespersonname) values (3,'joe')   insert @region (regionid, regionname) values (1,'north')  insert @region (regionid, regionname) values (2,'south')  insert @region (regionid, regionname) values (3,'east')  insert @region (regionid, regionname) values (4,'west')   insert @salespersonregion (salespersonid,regionid) values (1,1) insert @salespersonregion (salespersonid,regionid) values (1,2) insert @salespersonregion (salespersonid,regionid) values (1,3) insert @salespersonregion (salespersonid,regionid) values (2,2) insert @salespersonregion (salespersonid,regionid) values (2,3) insert @salespersonregion (salespersonid,regionid) values (2,4) insert @salespersonregion (salespersonid,regionid) values (3,1) insert @salespersonregion (salespersonid,regionid) values (3,4) 

a simple select me each sales person, each of salesperson's region.

select      sp.salespersonid,     sp.salespersonname,     r.regionname @salespersonregion spr     join @salesperson sp         on spr.salespersonid = sp.salespersonid     join @region r         on spr.regionid = r.regionid  

in case, return 9 rows.

i results this:

salespersonid    salespersonname      regions 1                jeff                 north,south,east 2                pat                  south,east,west 3                joe                  north,west 

select    sp.salespersonid,    sp.salespersonname,    regions = stuff   (     (       select ',' + r.regionname        @region r        inner join @salespersonregion spr        on r.regionid = spr.regionid        spr.salespersonid = sp.salespersonid        order r.regionid        xml path(''), type     ).value('.[1]','nvarchar(max)'),     1,1,''   ) @salesperson sp order sp.salespersonid;