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中实现多行一列值合并的几种方式,可根据具体情况,选择最合适的方法来实现数据合并。