![](/style/images/good.png)
![](/style/images/bad.png)
Concatenate a single column in a comma-delimited list
source link: https://www.codesd.com/item/concatenate-a-single-column-in-a-comma-delimited-list.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Concatenate a single column in a comma-delimited list
This question already has an answer here:
- Concatenate many rows into a single text string? 38 answers
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
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;
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK