oracle 合并中文,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