如何将多行作为字符串合并为一行? [英] How to get multiple rows into one line as a string?

问题描述

我有两个一对多"表:

表 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 不是单个字符串:

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

我尝试申请:

DECLARE @emails VARCHAR(999)

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

但没有运气.

应该如何解决?

谢谢.

推荐答案

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

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

这篇关于如何将多行作为字符串合并为一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！