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--