Concat Column Of Multiple Rows To Column Of Single Row

一个连接找出了这么多方法,的确原帖很强大。

oracle—多行转为一行的连接手段
http://blog.csdn.net/leisore/article/details/6186827

将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)

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.