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 varchar2(4000),  currentseprator varchar2(8),  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;

View Code

 

Oracle多行記錄合并自訂函數STRCAT

3 行轉列函數，wmsys.wm_concat()、LISTAGG()等

a WMSYS下的東西，一般功能不應該使用，

但我們也可以試試，如果查詢出的值提示為CLOB的話，不要驚慌，前面再加一個to_char()就好了，具體參考

oracle合并列的函數wm_concat的使用詳解

b 行轉列函數listagg()還是不錯的

下面這個例子，作者自己構造了一個表進行轉換，合并行，當然也可以合并值，但不合并行，具體參考

Oracle 列轉行函數 Listagg()

用系統內建的表進行示範下

------語句1select 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多行記錄合并的幾種方法