oracle中字符串合并与拆分

示例是在oralce示例数据库中执行

如现有需求要查找每个部门中的员工

SELECT DEPTNO, ENAME FROM EMP;

 

这样的结果并不是很直观，我们希望同部门的显示一行记录

------------------------------------------字符串合并-----------------------------------------------------------

with x1 as 
   (select deptno,
           ename, 
           row_number() over (partition by deptno order by ename) as rn from emp)
--select * from x1;
---用sys_connect_by_path合并字符串
select deptno,substr(sys_connect_by_path(ename,','),2)
from x1
where connect_by_isleaf=1
start with rn=1
connect by (prior rn)=rn-1
and (prior deptno)=deptno

 

 

oralce11g可以改为listagg

select deptno,listagg(ename,',')  within group (order by empno) from emp group by deptno;

 

 

很多人习惯用 wm_concat

但是它oracle一个未公开的内部函数，不同版本中返回类型也能存在差异（varchar或clob）

select deptno,wm_concat(ename) from emp group by deptno;

 

-------------------------------------------反操作-------------------------------------------------------------

with x2 as 
(select deptno,listagg(ename,',') within group (order by empno) as ename from emp  group by deptno)
select deptno,regexp_substr(ename,'[^,]+',1,level,'i')
from x2
connect by level<=regexp_count(ename,',')+1
and (prior deptno)=deptno
and (prior dbms_random.value()) is not null