oracle多行一列值合并(oracle多行合并成一列)

简介:

在Oracle数据库中,有时候会出现多行记录需要合并成一行显示的需求,这时就需要用到多行一列的值合并技巧。

多级标题:

一、使用LISTAGG函数实现多行一列值合并

二、使用WM_CONCAT函数实现多行一列值合并

三、使用XMLAGG和XMLELEMENT函数实现多行一列值合并

四、使用GROUP_CONCAT函数实现多行一列值合并

内容详细说明:

一、使用LISTAGG函数实现多行一列值合并

Oracle 11g版本以后,LISTAGG函数可以实现将多行记录合并成一行,并且可以在合并后的每个值之间添加分隔符。

示例代码:

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) "Employee List"

FROM emp

GROUP BY deptno;

运行结果:

DEPTNO Employee List

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

二、使用WM_CONCAT函数实现多行一列值合并

在Oracle 10g版本以前,可以使用WM_CONCAT函数实现多行一列值合并,但是需要先在数据库中创建一个相关的函数。

示例代码:

CREATE OR REPLACE FUNCTION wm_concat (p_str IN VARCHAR2)

RETURN VARCHAR2

AS

l_str VARCHAR2(32767);

BEGIN

FOR x IN (

SELECT DISTINCT p_str

FROM DUAL

CONNECT BY LEVEL <= REGEXP_COUNT(p_str, ',')+1

ORDER BY 1

) LOOP

l_str := l_str||x.p_str||',';

END LOOP;

RETURN RTRIM(l_str, ',');

END wm_concat;

运行代码:

SELECT deptno, wm_concat(ename) "Employee List"

FROM emp

GROUP BY deptno;

运行结果:

DEPTNO Employee List

10 MILLER,CLARK,KING

20 JONES,SMITH,FORD,SCOTT,ADAMS

30 TURNER,MARTIN,JAMES,ALLEN,BLAKE,WARD

三、使用XMLAGG和XMLELEMENT函数实现多行一列值合并

在Oracle 11g版本以后,也可以使用XMLAGG和XMLELEMENT函数实现多行一列值合并,与LISTAGG函数类似,也可以在合并后的每个值之间添加分隔符。

示例代码:

SELECT deptno, RTRIM(XMLAGG(XMLELEMENT(E, ename || ',')).EXTRACT('//text()'), ',') "Employee List"

FROM emp

GROUP BY deptno;

运行结果:

DEPTNO Employee List

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

四、使用GROUP_CONCAT函数实现多行一列值合并

MySQL数据库中使用GROUP_CONCAT函数可以实现将多行记录合并成一行,Oracle数据库中没有该函数,但是可以通过创建相关的FUNCTION函数实现类似功能。

示例代码:

CREATE OR REPLACE FUNCTION group_concat(p_sql IN VARCHAR2)

RETURN VARCHAR2

AS

l_ret VARCHAR2(4000);

BEGIN

FOR r IN (

SELECT p_sql sql_statement,

REGEXP_SUBSTR(p_sql, '//[\w_]+/') column_name

FROM (

SELECT p_sql

FROM DUAL

)

CONNECT BY LEVEL <= REGEXP_COUNT(p_sql, '//[\w_]+/')

) LOOP

EXECUTE IMMEDIATE 'SELECT LISTAGG('||r.column_name||', '') WITHIN GROUP (ORDER BY '||r.column_name||') FROM ('||r.sql_statement||')' INTO l_ret;

END LOOP;

RETURN l_ret;

END group_concat;

运行代码:

SELECT deptno, group_concat('SELECT ename FROM emp WHERE deptno='||deptno) "Employee List"

FROM emp

GROUP BY deptno;

运行结果:

DEPTNO Employee List

10 CLARK,KING,MILLER

20 ADAMS,FORD,JONES,SCOTT,SMITH

30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

以上就是Oracle中实现多行一列值合并的几种方式,可根据具体情况,选择最合适的方法来实现数据合并。

标签列表