独角兽企业重金招聘Python工程师标准>>>
wmsys.wm_concat函数，oracle 10g引入的；
作用：连接字符串，也可以用于将多行数据，转换为一行。
1.用于行列转换
SQL> select * from a;ITEMIDTYPEID TYPENAME----------- ------------------------- ----------12 bbb23 ccc25 ddd35 ddd11 aaaSQL> select * from b;ITEMID ITEMNAME-------------------- ---------1 kkk2 lll3 oooSQL> select a.itemid,b.itemname,wmsys.wm_concat(a.typename)2 from a,b3 where a.itemid=b.itemid4 group by a.itemid,b.itemname;ITEMID ITEMNAME WMSYS.WM_CONCAT(A.TYPENAME)------------------ ---------- -------------------------------1 kkk bbb,aaa2 lll ccc,ddd3 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 cl13 from dual4 connect by5level<=length('SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL')-6length(replace('SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MIL',',',''))+1;CL1--------------------------------------------------------------------------------SMITHALLENWARDJONESMARTINBLAKECLARKSCOTTKINGTURNERADAMSJAMESFORDMIL
--the end--
