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.

Answer 1

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