WM_CONCAT 函數的用法
select t.rank, t.Name from t_menu_item t;
<wbr><wbr><wbr> 10 CLARK<br><wbr><wbr><wbr> 10 KING<br><wbr><wbr><wbr> 10 MILLER<br><wbr><wbr><wbr> 20 ADAMS<br><wbr><wbr><wbr> 20 FORD<br><wbr><wbr><wbr> 20 JONES<br><wbr><wbr><wbr> 20 SCOTT<br><wbr><wbr><wbr> 20 SMITH<br><wbr><wbr><wbr> 30 ALLEN<br><wbr><wbr><wbr> 30 BLAKE<br><wbr><wbr><wbr> 30 JAMES<br><wbr><wbr><wbr> 30 MARTIN<br><wbr><wbr><wbr> 30 TURNER<br><wbr><wbr><wbr> 30 WARD<br>
--------------------------------<br>
我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果<br><wbr><wbr><wbr>select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;<br>
DEPTNO ENAME<br>
------ ----------<br><wbr><wbr><wbr> 10 CLARK, KING, MILLER<br><wbr><wbr><wbr> 20 ADAMS, FORD, JONES, SCOTT, SMITH<br><wbr><wbr><wbr> 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
例子如下:
SQL> create table idtable (id number,name varchar2(30));
Table created
SQL> insert into idtable values(10,'ab');
1 row inserted
SQL> insert into idtable values(10,'bc');
1 row inserted
SQL> insert into idtable values(10,'cd');
1 row inserted
SQL> insert into idtable values(20,'hi');
1 row inserted
SQL> insert into idtable values(20,'ij');
1 row inserted
SQL> insert into idtable values(20,'mn');
1 row inserted
SQL> select * from idtable;
<wbr><wbr><wbr><wbr><wbr><wbr><wbr> ID NAME<br>
---------- ------------------------------<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 bc<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 hi<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 ij<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 mn</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
6 rows selected
SQL> select id,wmsys.wm_concat(name) name from idtable
2 group by id;
<wbr><wbr><wbr><wbr><wbr><wbr><wbr> ID NAME<br>
---------- --------------------------------------------------------------------------------<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc,cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 hi,ij,mn</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;
<wbr><wbr><wbr><wbr><wbr><wbr><wbr> ID NAME<br>
---------- --------------------------------------------------------------------------------<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc,cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc,cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc,cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 ab,bc,cd,hi,ij,mn<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 ab,bc,cd,hi,ij,mn<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 ab,bc,cd,hi,ij,mn</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
6 rows selected
SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;
<wbr><wbr><wbr><wbr><wbr><wbr><wbr> ID NAME<br>
---------- --------------------------------------------------------------------------------<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc,cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 ab,bc,cd,hi<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 ab,bc,cd,hi,ij<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 ab,bc,cd,hi,ij,mn</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
6 rows selected
个人觉得这个用法比较有趣.
SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;
<wbr><wbr><wbr><wbr><wbr><wbr><wbr> ID NAME<br>
---------- --------------------------------------------------------------------------------<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc,cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc,cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab,bc,cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 hi,ij,mn<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 hi,ij,mn<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 hi,ij,mn</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
6 rows selected
SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;
<wbr><wbr><wbr><wbr><wbr><wbr><wbr> ID NAME<br>
---------- --------------------------------------------------------------------------------<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 ab<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 bc<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 10 cd<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 hi<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 ij<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 20 mn</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
6 rows selected
分享到:
相关推荐
wm_concat函数在oracle 10G以下版本是没有的,这个就需要我们自己来创建,有需要的就下载下来,直接运行就行了,如果在运行中出中,请分开运行,不要一次性运行哟
wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/
oracle 9i 实现wm_concat函数 进行一列多行合并,之间使用,分割
Oracle 11g之后取消了wm_concat函数,12C及以后版本需要使用的话,需要自定义新建这个函数,提供DDL给需要用到的兄弟们。
重建WMSYS用户的WMSYS.WM_CONCAT函数的3个文件重建WMSYS用户的WMSYS.WM_CONCAT函数的3个文件
解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。 简单来说,用PL/SQL执行下一下几个脚本就可以了。 特别要注意:PL/SQL登录时,要...
Oracle10g之后有些版本已不包含WMSYS.WM_CONCAT函数,若用到此函数, 系统会提示异常:ORA-00904: "WM_CONCAT": invalid identifier 因此需单独重建此函数,方法如下: 解压附件,在sqlplus中执行包里的owmctab....
大家用oracle数据库里面的wmsys.wm_concat函数有没有遇见过字符串缓冲区太小的情况? 针对这种情况我写了个自定义聚合函数 是clob版的。
Oracle从12C版本开始,不支持wm_concat函数,我们可以采取的办法有使用listagg函数代替wm_concat函数,或者为了减小修改程序的工作量,可以通过手工创建wm_concat函数来解决这个问题。
owmctab.plb、owmaggrs.plb、owmaggrb.plb
1、下载三个文件:owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 2、用sqlplus登录:sqlplus -logon sys/123 as sysdba 3、执行@C:\Users\JOYTRAVEL\Desktop\WMSYS用户\owmaggrb.plb; 如果执行结果报错,说找不...
Oracle新版中不支持 WM_CONCAT的处理方法,直接创建一个自定义函数代替
包含owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 三个文件,可解决OracleXE中没有WM_CONCAT函数的问题;解决方案如下:1、下载三个文件:owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 2、用sqlplus登录:...
11gr2和12C上已经摒弃了wm_concat函数,当时我们很多程序员在程序中使用了该函数,导致程序出现错误,为了减轻程序员修改程序的工作量,只有通过手工创建个wm_concat函数,来临时解决该问题
是处理wm_concat中以varchar处理合并列字段过小。 clob 可以加大处理。 oracle 9I oracle 10G 必备
ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案
解决WM_CONCAT()函数无法使用问题的相关资源包,相关解决方案见https://blog.csdn.net/u012556249/article/details/118111110
wmsys_wm_concat的几个用法/wmsys_wm_concat的几个用法/wmsys_wm_concat的几个用法/wmsys_wm_concat的几个用法/wmsys_wm_concat的几个用法
主要解决oracle低版本(例如9i)无法使用WM_CONCAT函数实现多行字段拼接问题,参考附件中样例即可
解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。