ORACLE 多行文本合并

表数据

C1	C2
1	C
1	B
1	A
2	A
3	A
3	C

结果数据

C1	LinkedText
1	C;A;B
2	A
3	A;C

方法一：利用CONNECT BY 实现

SELECT DISTINCT C1,
              FIRST_VALUE(LTRIM(TEXT, ';')) OVER(PARTITION BY C1 ORDER BY LVL DESC) LinkedText
FROM (SELECT C1, C2, LEVEL AS LVL, SYS_CONNECT_BY_PATH(C2, ';') AS TEXT
        FROM (SELECT C1,
                     C2,
                     ROW_NUMBER() OVER(PARTITION BY T1.C1 ORDER BY T1.C1) X
                FROM T1
               ORDER BY T1.C1) T2
      CONNECT BY C1 = PRIOR C1
             AND X - 1 = PRIOR X) T3
ORDER BY C1

方法二：自定义 聚合函数

1）定义 strcat_type 对象：

create or replace type strcat_type as object

(
  -- Author  : ADMINISTRATOR
  -- Created : 2013-8-7 23:49:51
  -- Purpose : String concatenation
  
  -- Attributes
  cat_string varchar2(4000),
  separator varchar2(1),
  
  -- Member functions and procedures
  static function ODCIAggregateInitialize(actx In Out strcat_type) return number,
  member function ODCIAggregateIterate(self In Out strcat_type,val in varchar2) return number,
  member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,
  member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number
)

2）实现 strcat_type 包体：

create or replace type body strcat_type is
  
  -- Member procedures and functions
  static function ODCIAggregateInitialize(actx IN OUT strcat_type) return number
  is
  begin
      actx := strcat_type(cat_string => null, separator =>  ';');
      return ODCIConst.Success;
  end;
  
  member function ODCIAggregateIterate(self IN OUT strcat_type, val IN varchar2 ) return number
  is
  begin
      self.cat_string := self.cat_string || self.separator || val;
      return ODCIConst.Success;
  end;
  
  member function ODCIAggregateTerminate(self IN Out strcat_type, returnValue OUT varchar2, flags IN number) return number
  is
  begin
      returnValue := ltrim(rtrim(self.cat_string,self.separator),self.separator);
      return ODCIConst.Success;
  end;


  member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN Out strcat_type) return number
  is
  begin
      self.cat_string := self.cat_string || self.separator || ctx2.cat_string;
      return ODCIConst.Success;
  end;

end;

3）创建函数

CREATE OR REPLACE FUNCTION strcat(input varchar2) RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;

4）示例

SELECT C1, STRCAT(C2) AS LINKEDTEXT FROM T1 T GROUP BY C1