如何将多行作为字符串放入一行？

Question

我有两个“一对多”表：

表格1

ID    Name
1     Abe
2     David
3     Orly

表2

ID    email
1     [e-mail]
1     [e-mail]
1     [e-mail]
2     [e-mail]
2     [e-mail]
3     [e-mail]
3     [e-mail]

我需要这样的输出：

1 Abe [e-mail], [e-mail], [e-mail]
2 David [e-mail], [e-mail]
3 Orly [e-mail], [e-mail]

我知道这行不通，因为内部SELECT不是单个字符串：

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

我尝试申请：

DECLARE @emails VARCHAR(999)

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

但没有运气。

应该如何解决呢？

谢谢。

Answer 1

实现这一目标的最巧妙的方法之一是将For XML Path和STUFF结合如下：

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