Concatenate a single column into a comma delimited list
Solution 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;
Solution 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
Solution 3
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
![Jeff Reddy](https://i.stack.imgur.com/bREtX.jpg?s=256&g=1)
Jeff Reddy
First and foremost, I'm a dad. Then I'm a golfer. Since golf doesn't pay the bills, I'm also a developer. Don't get me wrong, I love what I do, it's just that I don't have the time outside of work to develop. Being a dad takes up most of my time at home, and when I have the time, I like to hit the links. I've been developing with Microsoft products since 1999. I started with Visual Basic 5.0, moved onto 6.0 and then .NET in 2003. Since then, I have worked with both VB .NET and C# working on a multitude of windows, web and service applications. Currently, my role has moved beyond developer and as much as I like to sit down and write some code, I find myself managing contractors, services and infrastructure more than coding. I still mentor developers from time to time, most often refactoring code others write that I do writing my own.
Updated on June 10, 2022Comments
-
Jeff Reddy about 2 years
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
-
Adir D almost 11 yearsI think the cross apply and additional nested selects just complicates things needlessly.
-
Adir D almost 11 yearsHow does this differ from my answer?
-
i-one almost 11 yearsIt seems it can live without
, TYPE
and.value('.[1]','nvarchar(max)')
, isn't it ? -
Adir D almost 11 years@i-one have you tried it with a region named
Cape Cod & Islands
? -
Roman Pekar almost 11 yearsIt 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
-
i-one almost 11 yearsYeah, I see
&
, good point. Is it the only point or are there more potentially? -
Bogdan Sahlean almost 11 yearsIn this case it's matter of style. Usually, I like to find/see all source tables/views/etc. in
FROM
clause (of parent query). -
Adir D over 10 years@i-one any non-XML-safe character, e.g.
>
->>
,<
-><
, etc.