Concatenate values from column in related table into a string

Question

I have 2 tables (1-n) in SQL Server 2008.

Example: 1 person - 'n' addresses

I want to select persons data and bring related addresses districts into a column (concatenated)

ID  Name
------------------------------------------
1   John Smith
2   Daniel

PersonID  DistrictName
------------------------------------------
1         Brooklin
1         SoHo
2         Upper West Side
2         Harlem

SELECT desired
------------------------------------------
John Smith   Brooklin, SoHo
Daniel       Upper West Side, Harlem

How could I do this in tsql?

Check this http://stackoverflow.com/questions/8516212/how-to-concatenate-values-with-same-id-in-sql — SNathan, Feb 21 '14 at 18:15
@SNathan this question has a low-quality response, with an external link. the article is well explained, but I'm sure that a relevant answer for this question would be some straightforward info here in SO. thanks! — Andre Figueiredo, Feb 24 '14 at 20:04

score 2 · Accepted Answer · answered Feb 21 '14 at 18:32

2

The "classic" way is to use FOR XML PATH. There are duplicates of this question, but even following those it's not trivial to get the query correct;

SELECT name,
  STUFF((
    SELECT ', ', districtname AS 'data()' 
    FROM district 
    WHERE ID=PersonID
    FOR XML PATH('')), 1, 2, '') districts
FROM person;

An SQLfiddle to test with.

answered Feb 21 '14 at 18:32

Joachim Isaksson

176,943
25
281
294

Choose this as answer because is the most clean.. – Andre Figueiredo Feb 21 '14 at 18:56

score 1 · Answer 2 · answered Feb 21 '14 at 18:34

Test Data

DECLARE @Person TABLE(ID INT,Name VARCHAR(100))
INSERT INTO @Person VALUES
(1,'John Smith'),(2,'Daniel')

DECLARE @Person_Address TABLE(PersonID INT,DistrictName VARCHAR(1000))
INSERT INTO @Person_Address VALUES
(1,'Brooklin'),(1,'SoHo'),
(2,'Upper West Side'),(2,'Harlem')

Query

SELECT P.Name
      ,STUFF((SELECT ', ' + DistrictName [text()]
              FROM @Person_Address
              WHERE PersonID = P.ID
              FOR XML PATH(''),TYPE).
              value('.','NVARCHAR(MAX)'),1 ,2,'') AS Address
FROM @Person P
GROUP BY P.Name,P.ID

Result Set

╔════════════╦═════════════════════════╗
║    Name    ║         Address         ║
╠════════════╬═════════════════════════╣
║ Daniel     ║ Upper West Side, Harlem ║
║ John Smith ║ Brooklin, SoHo          ║
╚════════════╩═════════════════════════╝

Concatenate values from column in related table into a string

2 Answers2