Combining a single column in a comma separated list

Question

I saw some examples of people rolling one column into a comma-separated list, but I need a little more.

Here is an example of the data and results that I 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 INTO @SalesPerson (SalesPersonID, SalesPersonName) VALUES (1,'Jeff') INSERT INTO @SalesPerson (SalesPersonID, SalesPersonName) VALUES (2,'Pat') INSERT INTO @SalesPerson (SalesPersonID, SalesPersonName) VALUES (3,'Joe') INSERT INTO @Region (RegionID, RegionName) VALUES (1,'North') INSERT INTO @Region (RegionID, RegionName) VALUES (2,'South') INSERT INTO @Region (RegionID, RegionName) VALUES (3,'East') INSERT INTO @Region (RegionID, RegionName) VALUES (4,'West') INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (1,1) INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (1,2) INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (1,3) INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (2,2) INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (2,3) INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (2,4) INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (3,1) INSERT INTO @SalesPersonRegion (SalesPersonID,RegionID) VALUES (3,4) 

A simple choice will give me each seller, with each of the regions of sellers.

 SELECT sp.SalesPersonID, sp.SalesPersonName, r.RegionName FROM @SalesPersonRegion spr JOIN @SalesPerson sp ON spr.SalesPersonID = sp.SalesPersonID JOIN @Region r ON spr.RegionID = r.RegionID 

In this case, it will return 9 rows.

I would like to get the results as follows:

 SalesPersonID SalesPersonName Regions 1 Jeff North,South,East 2 Pat South,East,West 3 Joe North,West 

Answer 1

 SELECT sp.SalesPersonID, sp.SalesPersonName, Regions = STUFF ( ( SELECT ',' + r.RegionName FROM @Region AS r INNER JOIN @SalesPersonRegion AS spr ON r.RegionID = spr.RegionID WHERE spr.SalesPersonID = sp.SalesPersonID ORDER BY r.RegionID FOR XML PATH(''), TYPE ).value('.[1]','nvarchar(max)'), 1,1,'' ) FROM @SalesPerson AS sp ORDER BY sp.SalesPersonID; 

Answer 2

Try this query:

 SELECT sp.SalesPersonID, sp.SalesPersonName, reg.Regions FROM @SalesPerson sp CROSS APPLY( -- or OUTER APPLY SELECT STUFF( (SELECT ','+r.RegionName FROM @Region r INNER JOIN @SalesPersonRegion spr ON r.RegionID = spr.RegionID WHERE spr.SalesPersonID = sp.SalesPersonID FOR XML PATH('')),1,1,'') AS Regions )reg; 

Results:

 SalesPersonID SalesPersonName Regions ------------- --------------- ---------------- 1 Jeff North,South,East 2 Pat South,East,West 3 Joe North,West 

Answer 3

 select sp.SalesPersonID, sp.SalesPersonName, stuff( ( select ',' + r.RegionName from @SalesPersonRegion as spr inner join @Region as r on r.RegionID = spr.RegionID where spr.SalesPersonID = sp.SalesPersonID for xml path(''), type ).value('.', 'nvarchar(max)') , 1, 1, '') from @SalesPerson as sp 

see sql script example