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

I have two tables "one to many":

Table1

ID    Name
1     Abe
2     David
3     Orly

Table2

ID    email
1     a@zz.com
1     ab@zz.com
1     abe@zz.com
2     dav@zz.com
2     d@zz.com
3     orly@zz.com
3     o@zz.com

I need an output like this:

1 Abe a@zz.com, ab@zz.com, abe@zz.com
2 David dav@zz.com, d@zz.com
3 Orly orly@zz.com, o@zz.com

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.

Answers


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

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   a@zz.com,ab@zz.com,abe@zz.com
2  David dav@zz.com,d@zz.com
3  Orly  orly@zz.com,o@zz.com

Need Your Help

What is a one-sentence explanation of how Accurev works?

git accurev

I understand git, Subversion, CVS and a myriad of other source control systems.

How to use Key Bindings instead of Key Listeners

java swing key-bindings keyevent key-events

I'm using KeyListeners in my code (game or otherwise) as the way for my on-screen objects to react to user key input. Here is my code: