- 浏览: 95164 次
- 性别:
- 来自: 深圳
文章分类
- 全部博客 (112)
- java (112)
- [书目20081126]转型:用对策略,做对事 (1)
- [转]c# winform 绘制圆角窗体 (1)
- Idiot's Note Four: 循环中continue和“if ”的细节注意 (1)
- ActiveMQ中消息游标 (1)
- http://sourceforge.net 打不开的解决办法 (1)
- 常用命令收集(系统设置) (1)
- jsessionid 问题分析 (1)
- JSP 页面不能解析EL表达式。 (1)
- SQLServer的备份语句 (1)
- Struts2 jQuery Plugin Showcase 学习笔记 (1)
- Razor view engine 基础语法 (1)
- 44个 灵感来自于“大自然”的网站设计(上) (1)
- 菜单多国语言化遇到的问题 (1)
- Mysql查看当前连接数 (1)
- Libgdx之旅-----LuaTutorial (1)
- 一些经典的BLOG (1)
- How do I prevent builds between multiple changes to the workspace? (1)
- RAP常用技巧 (1)
- Android常用类库包介绍 (1)
- 如何让新人尽快融入项目团队 (1)
- SharpDevelop (1)
- 生成PDF文件的Java库iText (1)
- 将Java包程序转换为Exe可执行文件 (1)
- hibernate hql 多个一查询 (1)
- 将视频放到网页上续 RM VS ASF (1)
- 程序员如何自我超越 (1)
- j2me实现手机通讯录的备份与还原 (1)
- Websphere 命令行部署与管理应用 (1)
- StackOverflow的404错误页面 (1)
- WM_CONCAT 函數的用法 (1)
- MyEclipse打jar包 (1)
- 通用分頁存儲過程. (1)
- 采用并行计算发挥多核CPU的威力 (1)
- mxgraph 之 增加对齐标线 等对graph的相关设置 (1)
- EJB中的@ejb注解 (1)
- UTMP分析 (1)
- .nET2.0小技巧 (1)
- spring security 和 struts 的filter顺序问题 (1)
- Session和Cookie的深入研究 (1)
最新评论
-
zhengyong7232:
<init-param> <param- ...
spring编码过滤器 -
emilyzhanghong:
你好:
请教下.你这里说的 B的war包中包含X的class ...
Maven中如何配WAR依赖WAR和JAR的多模块项目结构 -
jiangcs520:
好赞
程序员如何自我超越 -
wodentt:
看不懂....
将Java包程序转换为Exe可执行文件 -
student_stu:
...
jsessionid 问题分析
<span style="font-family: Times New Roman; font-size: 16px;">WM_CONCAT 函數的用法</span>
<span style="font-family: Times New Roman;">[size=16px;]select t.rank, t.Name from t_menu_item t;<br><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>[/size]</span>
<span style="font-family: Times New Roman; font-size: 16px;">例子如下:</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> create table idtable (id number,name varchar2(30));</span>
<span style="font-family: Times New Roman; font-size: 16px;">Table created</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> insert into idtable values(10,'ab');</span>
<span style="font-family: Times New Roman; font-size: 16px;">1 row inserted</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> insert into idtable values(10,'bc');</span>
<span style="font-family: Times New Roman; font-size: 16px;">1 row inserted</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> insert into idtable values(10,'cd');</span>
<span style="font-family: Times New Roman; font-size: 16px;">1 row inserted</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> insert into idtable values(20,'hi');</span>
<span style="font-family: Times New Roman; font-size: 16px;">1 row inserted</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> insert into idtable values(20,'ij');</span>
<span style="font-family: Times New Roman; font-size: 16px;">1 row inserted<br>
SQL> insert into idtable values(20,'mn');</span>
<span style="font-family: Times New Roman; font-size: 16px;">1 row inserted</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> select * from idtable;</span>
<span style="font-family: Times New Roman;">[size=16px;]<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>[/size]</span>
<span style="font-family: Times New Roman; font-size: 16px;">6 rows selected<br>
SQL> select id,wmsys.wm_concat(name) name from idtable<br>
2 group by id;</span>
<span style="font-family: Times New Roman;">[size=16px;]<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>[/size]</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;</span>
<span style="font-family: Times New Roman;">[size=16px;]<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>[/size]</span>
<span style="font-family: Times New Roman; font-size: 16px;">6 rows selected</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;</span>
<span style="font-family: Times New Roman;">[size=16px;]<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>[/size]</span>
<span style="font-family: Times New Roman; font-size: 16px;">6 rows selected</span>
<span style="font-family: Times New Roman; font-size: 16px;">个人觉得这个用法比较有趣.</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;</span>
<span style="font-family: Times New Roman;">[size=16px;]<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>[/size]</span>
<span style="font-family: Times New Roman; font-size: 16px;">6 rows selected</span>
<span style="font-family: Times New Roman; font-size: 16px;">SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;</span>
<span style="font-family: Times New Roman;">[size=16px;]<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>[/size]</span>
<span style="font-family: Times New Roman; font-size: 16px;">6 rows selected</span>
发表评论
-
Session和Cookie的深入研究
2012-02-08 14:42 749Session和Cookie的深入研究 作者:孙鑫 ... -
spring security 和 struts 的filter顺序问题
2012-02-08 12:03 1015使用spring security3 和struts2 ... -
.nET2.0小技巧
2012-02-04 15:09 716<div class="pos ... -
UTMP分析
2012-02-03 10:34 780[size=small;]分析引擎包含:<br& ... -
EJB中的@ejb注解
2012-02-02 12:24 903(本文是转载其他人的技术文章,觉得说得挺浅显易懂,特借 ... -
mxgraph 之 增加对齐标线 等对graph的相关设置
2012-02-02 11:49 1578graph.setConnectable(tru ... -
采用并行计算发挥多核CPU的威力
2012-02-01 09:24 707<p><strong>< ... -
通用分頁存儲過程.
2012-01-11 13:29 752MSSQL中要想分頁只能借助 row_number() ... -
MyEclipse打jar包
2011-12-28 14:43 946<span style="font-f ... -
StackOverflow的404错误页面
2011-12-21 17:28 836<span style="color: ... -
Websphere 命令行部署与管理应用
2011-12-21 11:44 922<span style="color: ... -
j2me实现手机通讯录的备份与还原
2011-12-20 13:44 940现在用的手机用了4年半了,其中摔过n次,但是从没坏过, ... -
程序员如何自我超越
2011-12-19 13:24 599<span style="font-f ... -
将视频放到网页上续 RM VS ASF
2011-12-17 15:59 838发现网页上包含RM视频文件的一个致命弱点,那就是必须将 ... -
hibernate hql 多个一查询
2011-12-15 16:49 916Session s=sessionFactory.ge ... -
将Java包程序转换为Exe可执行文件
2011-12-14 10:39 811<p class="MsoNorma ... -
生成PDF文件的Java库iText
2011-12-14 09:44 708<p class="MsoNo ... -
SharpDevelop
2011-12-13 14:54 1162SharpDevelop是一个DotNet平台下的免费 ... -
如何让新人尽快融入项目团队
2011-12-13 10:54 711刚进公司的新人,从心理上要有个从学校到公司的转变的过 ... -
Android常用类库包介绍
2011-12-12 13:54 789<p class="MsoNorm ...
相关推荐
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三个脚本即可。