如何在SQL中的单行中连接多行？ [英] How to concatenate multiple rows inside a single row in SQL?

问题描述

如何使用SQL将多行连接到单个行？我的数据库是DB2

How do I concatenate multiple rows into a single row using SQL? My database is DB2

TableFoo
 -------
 Id      Name
 1       Apples
 1       Tomatoes
 1       Potatoes
 2       Banana
 2       Peach

我想要一些类似

ID       FruitsAvailable
-------------------------
 1       Apples, Tomatoes, Potatoes

推荐答案

尝试这个

  SELECT id ,FruitsAvailable 
  FROM
      (SELECT id , group_concat(Name) as FruitsAvailable 
       FROM  TableFoo
       WHERE id = 1) t

HERE DEMO SQLFIDDLE

编辑：
在db2中，您需要创建函数，然后调用它

in db2 you need to create function and then call it

 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  ; 

然后在这里调用

     Select  id, MyUDF(Name) as FruitsAvailable
     From TableFoo 
     where id = 1 

这篇关于如何在SQL中的单行中连接多行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！