SQL 如何将多行作为字符串合并为一行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7958816/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get multiple rows into one line as a string?
提问by elarrow
I have two tables "one to many":
我有两个“一对多”表：
Table1
表格1
ID Name
1 Abe
2 David
3 Orly
Table2
表2
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 不是单个字符串：
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.
谢谢。
回答by Jon Egerton
One of the neatest ways to achieve this is to combine For XML Path and STUFF as follows:
实现此目的的最巧妙方法之一是将 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
回答by Bogdan Sahlean
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 [email protected],[email protected],[email protected]
2 David [email protected],[email protected]
3 Orly [email protected],[email protected]
