Concatenate a single column into a comma delimited list

Question

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

Take a look at this approach: http://codecorner.galanter.net/2009/06/25/t-sql-string-aggregate-in-sql-server/ — Yuriy Galanter, Aug 09 '13 at 20:47

score 9 · Accepted Answer · answered Aug 09 '13 at 20:48

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;

answered Aug 09 '13 at 20:48

Aaron Bertrand

272,866
37
466
490

It seems it can live without `, TYPE` and `.value('.[1]','nvarchar(max)')`, isn't it ? – i-one Aug 09 '13 at 20:58
1

@i-one have you tried it with a region named `Cape Cod & Islands`? – Aaron Bertrand Aug 09 '13 at 21:02
Yeah, I see `&`, good point. Is it the only point or are there more potentially? – i-one Aug 09 '13 at 21:07
@i-one any non-XML-safe character, e.g. `>` -> `>`, `<` -> `<`, etc. – Aaron Bertrand Feb 02 '14 at 02:26

score 2 · Answer 2 · answered Aug 09 '13 at 20:48

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

answered Aug 09 '13 at 20:48

Bogdan Sahlean

19,233
3
42
57

I think the cross apply and additional nested selects just complicates things needlessly. – Aaron Bertrand Aug 09 '13 at 20:56
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). – Bogdan Sahlean Aug 09 '13 at 21:20

Roman Pekar · Answer 3 · 2013-08-09T20:54:30.640

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

edited Aug 09 '13 at 20:54

answered Aug 09 '13 at 20:49

Roman Pekar

107,110
28
195
197

1

How does this differ from my answer? – Aaron Bertrand Aug 09 '13 at 20:56
1

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 – Roman Pekar Aug 09 '13 at 21:04

Concatenate a single column into a comma delimited list

3 Answers3

Linked