Oracle多行记录合并的几种方法

今天正好遇到需要做这个功能，顺手搜了一下网络，把几种方法都列出来，方便以后参考。

1 什么是合并多行字符串（连接字符串）呢，例如：

SQL> desc test;
Name Type Nullable Default Comments
------- ------------ -------- ------- --------
COUNTRY VARCHAR2(20) Y
CITY VARCHAR2(20) Y
SQL> select * from test;
COUNTRY CITY
-------------------- --------------------
中国台北
中国香港
中国上海
日本东京
日本大阪
要求得到如下结果集：
------- --------------------
中国台北,香港,上海
日本东京，大阪

其实网友已经有一个汇总的解决方案了，如下

Oracle 多行记录合并/连接/聚合字符串的几种方法

2 通过自定义函数的方法

这个方法比较灵活，随时可调用该函数，推荐使用，由于原网页代码有误，我这里再贴一遍

--❶
CREATE OR REPLACE FUNCTION strcat (input VARCHAR2)
RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;
--❷
create or replace type strcat_type as object
 (
 currentstr ),
 currentseprator ),
 static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number,
 member function ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number,
 member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number,
 member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number)

--❸
create or replace type body strcat_type is
 static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is
 begin
 sctx := strcat_type('',',');
 return ODCIConst.Success;
 end;
 member function ODCIAggregateIterate(self IN OUT strcat_type,
value IN VARCHAR2) return number is
 begin
 if self.currentstr is null then
 self.currentstr := value;
 else
 self.currentstr := self.currentstr ||currentseprator || value;
 end if;
 return ODCIConst.Success;
 end;
 member function ODCIAggregateTerminate(self IN strcat_type,
 returnValue OUT VARCHAR2, flags IN number) return number is
 begin
 returnValue := self.currentstr;
 return ODCIConst.Success;
 end;
 member function ODCIAggregateMerge(self IN OUT strcat_type,
ctx2 IN strcat_type) return number is
 begin
 if ctx2.currentstr is null then
 self.currentstr := self.currentstr;
 elsif self.currentstr is null then
 self.currentstr := ctx2.currentstr;
 else
 self.currentstr := self.currentstr || currentseprator || ctx2.currentstr;
 end if;
 return ODCIConst.Success;
 end;

Oracle多行记录合并自定义函数STRCAT

3 行转列函数，wmsys.wm_concat()、LISTAGG()等

a WMSYS下的东西，一般功能不应该使用，

但我们也可以试试，如果查询出的值提示为CLOB的话，不要惊慌，前面再加一个to_char()就好了，具体参考

oracle合并列的函数wm_concat的使用详解

b 行转列函数listagg()还是不错的

下面这个例子，作者自己构造了一个表进行转换，合并行，当然也可以合并值，但不合并行，具体参考

Oracle 列转行函数 Listagg()

用系统自带的表进行演示下

------语句1
select cc.mgr,listagg(cc.ename,',') within GROUP (order by cc.mgr) from scott.emp cc group by cc.mgr
------语句2 请注意这两句查询结果的不同
select cc.mgr,listagg(cc.ename,',') within GROUP(order by cc.mgr) over (partition by cc.mgr) rank from scott.emp cc

结果如下

结果1

^{1 7566 FORD,SCOTT
2 7698 ALLEN,JAMES,MARTIN,TURNER,WARD}

3 7782 MILLER

4 7788 ADAMS

5 7839 BLAKE,CLARK,JONES

6 7902 SMITH

7 KING

结果2

^{1 7566 FORD,SCOTT

2 7566 FORD,SCOTT}

3 7698 JAMES,ALLEN,WARD,TURNER,MARTIN

4 7698 JAMES,ALLEN,WARD,TURNER,MARTIN

5 7698 JAMES,ALLEN,WARD,TURNER,MARTIN

6 7698 JAMES,ALLEN,WARD,TURNER,MARTIN

7 7698 JAMES,ALLEN,WARD,TURNER,MARTIN

8 7782 MILLER

9 7788 ADAMS

10 7839 BLAKE,JONES,CLARK

11 7839 BLAKE,JONES,CLARK

12 7839 BLAKE,JONES,CLARK

13 7902 SMITH

14 KING

网络资源，原作者可要求删除，谢谢

秒客网

Oracle多行记录合并的几种方法

1 什么是合并多行字符串（连接字符串）呢，例如：

2 通过自定义函数的方法

3 行转列函数，wmsys.wm_concat()、LISTAGG()等

相关文章