今天遇到将多行转为一行的一个操作,多谢oracle开发板的 wildwave 提供了比较通用的解决办法,同时也将自己搜到的这方面资料整理如下,多是用于连接列值的。
String集聚连接技术
需要将多行转换为一行,例子如下:
基础数据: DEPTNO ENAME ---------- ---------- 20 SMITH 30 ALLEN 30 WARD 20 JONES 30 MARTIN 30 BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER 20 ADAMS 30 JAMES 20 FORD 10 MILLER 预期输出: DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
- LISTAGG分析函数(11g Release 2)
- WM_CONCAT内建函数
- 自定义函数
- 使用Ref Cursor实现通用函数
- 用户自定义聚集函数
- ROW_NUMBER()和SYS_CONNECT_BY_PATH函数(Oracle 9i)
- COLLECT函数(Oracle 10g)
LISTAGG分析函数(11g Release 2)
Oracle 11g Release 2介绍了LISTAGG
函数,使得聚集连接字符串变得很容易。并且允许使用我们 指定连接串中的字段顺序。使用LISTAGG如下:
COLUMN employees FORMAT A50 SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected.
WM_CONCAT内建函数
如果你的Oracle不是11g Release 2,但是支持WM_CONCAT函数,那么解决上面的问题同样是小菜一碟,使用WM_CONCAT 函数G如下:
COLUMN employees FORMAT A50 SELECT deptno, wm_concat(ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD 3 rows selected.
自定义函数
另一个方法是自定义一个函数解决问题。get_employees对于给定部门返回一组员工:
CREATE OR REPLACE FUNCTION get_employees (p_deptno in emp.deptno%TYPE) RETURN VARCHAR2 IS l_text VARCHAR2(32767) := NULL; BEGIN FOR cur_rec IN (SELECT ename FROM emp WHERE deptno = p_deptno) LOOP l_text := l_text || ',' || cur_rec.ename; END LOOP; RETURN LTRIM(l_text, ','); END; / SHOW ERRORS
COLUMN employees FORMAT A50 SELECT deptno, get_employees(deptno) AS employees FROM emp GROUP by deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected.
为了改善性能减少函数调用,我们预先过滤行数。.
COLUMN employees FORMAT A50 SELECT e.deptno, get_employees(e.deptno) AS employees FROM (SELECT DISTINCT deptno FROM emp) e; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected.
使用Ref Cursor实现通用函数
另一个可替代的方法是使用游标变量写一个函数来连接行值。基本和上面一样,只是传入的是一个游标,所以使得它更通用:.
CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN SYS_REFCURSOR) RETURN VARCHAR2 IS l_return VARCHAR2(32767); l_temp VARCHAR2(32767); BEGIN LOOP FETCH p_cursor INTO l_temp; EXIT WHEN p_cursor%NOTFOUND; l_return := l_return || ',' || l_temp; END LOOP; RETURN LTRIM(l_return, ','); END; / SHOW ERRORS
使用如下:
COLUMN employees FORMAT A50 SELECT e1.deptno, concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees FROM emp e1 GROUP BY e1.deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected.
同样的,为了减少函数调用可以预先顾虑一些行。.
COLUMN employees FORMAT A50 SELECT deptno, concatenate_list(CURSOR(SELECT e2.ename FROM emp e2 WHERE e2.deptno = e1.deptno)) employees FROM (SELECT DISTINCT deptno FROM emp) e1; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected.
用户自定义聚集函数
如果你不想使用内置的函数,你可以自定义聚集函数:
CREATE OR REPLACE TYPE t_string_agg AS OBJECT ( g_string VARCHAR2(32767), STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2 ) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER, MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER ); / SHOW ERRORS CREATE OR REPLACE TYPE BODY t_string_agg IS STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg) RETURN NUMBER IS BEGIN sctx := t_string_agg(NULL); RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg, value IN VARCHAR2 ) RETURN NUMBER IS BEGIN SELF.g_string := self.g_string || ',' || value; RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER IS BEGIN returnValue := RTRIM(LTRIM(SELF.g_string, ','), ','); RETURN ODCIConst.Success; END; MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg, ctx2 IN t_string_agg) RETURN NUMBER IS BEGIN SELF.g_string := SELF.g_string || ',' || ctx2.g_string; RETURN ODCIConst.Success; END; END; / SHOW ERRORS CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING t_string_agg; / SHOW ERRORS
使用如下:
COLUMN employees FORMAT A50 SELECT deptno, string_agg(ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD 3 rows selected.
ROW_NUMBER()和SYS_CONNECT_BY_PATH函数(Oracle 9i)
使用 ROW_NUMBER()
和SYS_CONNECT_BY_PATH
实现:
SELECT deptno, LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees FROM (SELECT deptno, ename, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev FROM emp) GROUP BY deptno CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno START WITH curr = 1; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 3 rows selected.
COLLECT函数(Oracle 10g)
使用COLLECT函数,这个需要一个关联数组:
CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000); / CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab IN t_varchar2_tab, p_delimiter IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS l_string VARCHAR2(32767); BEGIN FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP IF i != p_varchar2_tab.FIRST THEN l_string := l_string || p_delimiter; END IF; l_string := l_string || p_varchar2_tab(i); END LOOP; RETURN l_string; END tab_to_string; /
使用如下:
COLUMN employees FORMAT A50 SELECT deptno, tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,JONES,SCOTT,ADAMS,FORD 30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES 3 rows selected.