title: How to get multiple rows into one line as a string?
body: <p>I have two tables "one to many":</p>
  
  <p>Table1</p>
  
  <pre><code>ID    Name
  1     Abe
  2     David
  3     Orly
  </code></pre>
  
  <p>Table2</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I need an output like this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I know this won't work, because the inner SELECT is not a single string:</p>
  
  <pre><code>SELECT 
      ID, Name, 
      (SELECT email FROM Table2  WHERE Table2.ID = Table1.ID) AS emails 
  FROM Table1
  </code></pre>
  
  <p>I tried to apply:</p>
  
  <pre><code>DECLARE @emails VARCHAR(999)
  
  SELECT [ID],[Name], 
       (SELECT @emails = COALESCE(@emails + ', ', '') + [email] 
        FROM Table2) AS 'emails' 
  FROM Table1
  </code></pre>
  
  <p>but with no luck.</p>
  
  <p>How should this be solved?</p>
  
  <p>Thanks.</p>