How to get multiple rows into one line as a string?

Question

I have two tables "one to many":

Table1

ID    Name
1     Abe
2     David
3     Orly

Table2

ID    email
1     [email protected]
1     [email protected]
1     [email protected]
2     [email protected]
2     [email protected]
3     [email protected]
3     [email protected]

I need an output like this:

1 Abe [email protected], [email protected], [email protected]
2 David [email protected], [email protected]
3 Orly [email protected], [email protected]

I know this won't work, because the inner SELECT is not a single string:

SELECT 
    ID, Name, 
    (SELECT email FROM Table2  WHERE Table2.ID = Table1.ID) AS emails 
FROM Table1

I tried to apply:

DECLARE @emails VARCHAR(999)

SELECT [ID],[Name], 
     (SELECT @emails = COALESCE(@emails + ', ', '') + [email] 
      FROM Table2) AS 'emails' 
FROM Table1

but with no luck.

How should this be solved?

Thanks.

[Coalesce (see prior Q&A)][1] [1]: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Eric Z Beard, Oct 31 '11 at 19:45
If you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Oct 31 '11 at 20:25

Jon Egerton · Accepted Answer · 2013-05-28T08:32:28.010

30

One of the neatest ways to achieve this is to combine For XML Path and STUFF as follows:

SELECT
    ID, Name, 
    Emails = STUFF((
        SELECT ', ' + Email FROM Table2 WHERE Table2.ID = Table1.ID
        FOR XML PATH ('')),1,2,'')
FROM Table1

edited May 28 '13 at 08:32

answered Oct 31 '11 at 20:57

Jon Egerton

40,401
11
97
129

2

Awesome, helped me out a ton. Thanks! – Techgration Oct 10 '12 at 23:10
And, you can add `group by ID, Name` to eliminate the extra rows. – James L. Sep 20 '16 at 20:25

Bogdan Sahlean · Answer 2 · 2011-10-31T20:54:14.183

9

SELECT  *
FROM    Table1 a
CROSS APPLY --or OUTER APPLY
(
    SELECT SUBSTRING(
        (SELECT ','+b.Email
        FROM    Table2 b
        WHERE   a.ID = b.ID
        FOR XML PATH(''))
        ,2
        ,4000) GroupConcat
) x

Results:

ID Name  GroupConcat
-- ----- -----------------------------
1  Abe   [email protected],[email protected],[email protected]
2  David [email protected],[email protected]
3  Orly  [email protected],[email protected]

edited Oct 31 '11 at 20:54

answered Oct 31 '11 at 20:45

Bogdan Sahlean

19,233
3
42
57

I had to add `distinct` to the outermost `select` to eliminate extra rows. – James L. Sep 20 '16 at 20:30

How to get multiple rows into one line as a string?

2 Answers2

Linked