oracle wmsys.wm_concat函数

 

wmsys.wm_concat函数，oracle 10g引入的；

作用：连接字符串，也可以用于将多行数据，转换为一行。

 

1.用于行列转换

SQL> select * from a; ITEMID TYPEID TYPENAME ----------- ------------------------- ---------- 1 2 bbb 2 3 ccc 2 5 ddd 3 5 ddd 1 1 aaa SQL> select * from b; ITEMID ITEMNAME -------------------- --------- 1 kkk 2 lll 3 ooo SQL> select a.itemid,b.itemname,wmsys.wm_concat(a.typename) 2 from a,b 3 where a.itemid=b.itemid 4 group by a.itemid,b.itemname; ITEMID ITEMNAME WMSYS.WM_CONCAT(A.TYPENAME) ------------------ ---------- ------------------------------- 1 kkk bbb,aaa 2 lll ccc,ddd 3 ooo ddd 

2.下面将emp表中所有员工姓名连接起来：

SQL> select wmsys.wm_concat(ename) from emp; WMSYS.WM_CONCAT(ENAME) -------------------------------------------------------------------------------- SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL 

那如果我们要将上面一行中员工的姓名区分开来，每个名字显示在一行怎么实现呢？

这要用到正则表达式函数regexp_substr();

SQL> select regexp_substr('SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL','[^,]+',1,level) 2 as cl1 3 from dual 4 connect by 5 level<=length('SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL')- 6 length(replace('SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL',',',''))+1; CL1 -------------------------------------------------------------------------------- SMITH ALLEN WARD JONES MARTIN BLAKE CLARK SCOTT KING TURNER ADAMS JAMES FORD MIL 

--the end--