6

I've seen multiple examples of people rolling up a single column into a comma delimited list, but I need a bit more.

Here is an example of the data and results 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 select will get me each sales person, with each of that salesperson's region.

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 results like this:

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

3 Answers 3

Reset to default
9 
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

It seems it can live without , TYPE and .value('.[1]','nvarchar(max)'), isn't it ?
@i-one have you tried it with a region named Cape Cod & Islands?
Yeah, I see &, good point. Is it the only point or are there more potentially?
@i-one any non-XML-safe character, e.g. > -> &gt;, < -> &lt;, etc.
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
Improve this answer

2 Comments

I think the cross apply and additional nested selects just complicates things needlessly.
In this case it's matter of style. Usually, I like to find/see all source tables/views/etc. in FROM clause (of parent query).
2 
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 fiddle example

Improve this answer

2 Comments

How does this differ from my answer?
It doesn t have [1] but in general you right this one is redundant here - i've spent too much time with formatting and sql fiddling

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.