如何在 SQL 中的一行中连接多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14851025/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 13:40:41 来源:igfitidea点击:
How to concatenate multiple rows inside a single row in SQL?
提问by Zo Has
How do I concatenate multiple rows into a single row using SQL? My database is DB2
如何使用 SQL 将多行连接成一行？我的数据库是 DB2
TableFoo
-------
Id Name
1 Apples
1 Tomatoes
1 Potatoes
2 Banana
2 Peach
I want something like
我想要类似的东西
ID FruitsAvailable
-------------------------
1 Apples, Tomatoes, Potatoes
回答by echo_Me
try this
尝试这个
SELECT id ,FruitsAvailable
FROM
(SELECT id , group_concat(Name) as FruitsAvailable
FROM TableFoo
WHERE id = 1) t
EDIT: in db2 you need to create function and then call it
编辑：在 db2 中，您需要创建函数然后调用它
CREATE FUNCTION MySchema/MyUDF (
PARCol2 CHAR(5) )
RETURNS VARCHAR(1024)
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
BEGIN
DECLARE ReturnVal VARCHAR(1024) NOT NULL DEFAULT '';
FOR CsrC1 AS C1 CURSOR
FOR SELECT MyCol1
FROM MyTable
WHERE MyCol2 = ParCol2
DO SET ReturnVal = ReturnVal Concat CsrC1.MyCol1;
END FOR;
RETURN LTRIM(ReturnVal);
END ;
and then call it here
然后在这里调用它
Select id, MyUDF(Name) as FruitsAvailable
From TableFoo
where id = 1
回答by Ravi Maggon
Use this query:
使用此查询：
SELECT Id, GROUP_CONCAT(Name SEPARATOR ', ') FROM TableFoo GROUP BY Id;
