select t.rank, t.Name from t_menu_item t;
10
CLARK
10
KING
10
MILLER
20
ADAMS
20
FORD
20
JONES
20
SCOTT
20
SMITH
30
ALLEN
30
BLAKE
30
JAMES
30
MARTIN
30
TURNER
30
WARD
--------------------------------
我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果
select
t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY
t.rank;
DEPTNO ENAME
------ ----------
10 CLARK,
KING, MILLER
20 ADAMS,
FORD, JONES, SCOTT, SMITH
30 ALLEN,
BLAKE, JAMES, MARTIN, TURNER, WARD
例子如下:
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;
ID NAME
---------- ------------------------------
10 ab
10 bc
10 cd
20 hi
20 ij
20 mn
6 rows selected
SQL> select id,wmsys.wm_concat(name) name from
idtable
2 group by id;
ID NAME
----------
--------------------------------------------------------------------------------
10 ab,bc,cd
20 hi,ij,mn
SQL> select id,wmsys.wm_concat(name) over (order
by id) name from idtable;
ID NAME
----------
--------------------------------------------------------------------------------
10 ab,bc,cd
10 ab,bc,cd
10 ab,bc,cd
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn
20 ab,bc,cd,hi,ij,mn
6 rows selected
SQL> select id,wmsys.wm_concat(name) over (order
by id,name) name from idtable;
ID NAME
----------
--------------------------------------------------------------------------------
10 ab
10 ab,bc
10 ab,bc,cd
20 ab,bc,cd,hi
20 ab,bc,cd,hi,ij
20 ab,bc,cd,hi,ij,mn
6 rows selected
个人觉得这个用法比较有趣.
SQL> select id,wmsys.wm_concat(name) over
(partition by id) name from idtable;
ID NAME
----------
--------------------------------------------------------------------------------
10 ab,bc,cd
10 ab,bc,cd
10 ab,bc,cd
20 hi,ij,mn
20 hi,ij,mn
20 hi,ij,mn
6 rows selected
SQL> select id,wmsys.wm_concat(name) over
(partition by id,name) name from idtable;
ID NAME
----------
--------------------------------------------------------------------------------
10 ab
10 bc
10 cd
20 hi
20 ij
20 mn
6 rows selected
ps:
wmsys.wm_concat、sys_connect_by_path、自定义行数实现行列转换:
CREATE TABLE tab_name(ID INTEGER
NOT NULL PRIMARY KEY,cName VARCHAR2(20));
CREATE TABLE tab_name2(ID INTEGER NOT NULL,pName
VARCHAR2(20));
INSERT INTO tab_name(ID,cName)
VALUES (1,'百度');
INSERT INTO tab_name(ID,cName) VALUES (2,'Google');
INSERT INTO tab_name(ID,cName) VALUES (3,'网易');
INSERT INTO tab_name2(ID,pName) VALUES (1,'研发部');
INSERT INTO tab_name2(ID,pName) VALUES (1,'市场部');
INSERT INTO tab_name2(ID,pName) VALUES (2,'研发部');
INSERT INTO tab_name2(ID,pName) VALUES (2,'平台架构');
INSERT INTO tab_name2(ID,pName) VALUES (3,'研发部');
COMMIT;
期望结果:
ID
cName
pName
1
百度
研发部,市场部
2
Google
研发部
3
网易
研发部,平台架构
方法一:使用wmsys.wm_concat()
SELECT
t1.ID,t1.cName,wmsys.wm_concat(t2.pName) FROM tab_name t1,tab_name2
t2 WHERE t1.ID=t2.ID GROUP BY t1.cName,t1.id;
方法二:使用sys_connect_by_path
select id, cName,
ltrim(max(sys_connect_by_path(pName, ',')), ',') from (select
row_number() over(PARTITION by t1.id ORDER by cName) r,t1.*,
t2.pName from tab_name t1, tab_name2 t2 where t1.id = t2.id)
start with r=1 CONNECT by prior r =r-1 and prior id = id group by
id ,cName order by id;
方法三:使用自定义函数
create or replace function
coltorow(midId INT) RETURN VARCHAR2 is
Result VARCHAR2(1000);
begin
FOR cur IN (SELECT pName FROM tab_name2 t2 WHERE midId=t2.id)
LOOP
RESULT:=RESULT||cur.pName||',';
END LOOP;
RESULT:=rtrim(RESULT,',');
return(Result);
end coltorow;
SELECT t1.*,coltorow(t1.ID) FROM
tab_name t1,tab_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.ID,t1.cname
ORDER BY t1.ID;
select dm, mc, wmsys.wm_concat(xd) xd,wmsys.wm_concat(mm) mm
from (select a.dm, a.mc, b.xd,c.mc mm
from jyj_xx a, jyj_xx_xz b,zd_xxlx c
where a.id = b.zbid and b.xd=c.dm
order by b.xd)
group by dm, mc;
分享到:
相关推荐
Oracle10g之后有些版本已不包含WMSYS.WM_CONCAT函数,若用到此函数, 系统会提示异常:ORA-00904: "WM_CONCAT": invalid identifier 因此需单独重建此函数,方法如下: 解压附件,在sqlplus中执行包里的owmctab....
大家用oracle数据库里面的wmsys.wm_concat函数有没有遇见过字符串缓冲区太小的情况? 针对这种情况我写了个自定义聚合函数 是clob版的。
wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/wmsys_wm_concat函数结果拆解/
ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案
重建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登录时,要...
解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。
1、下载三个文件:owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb ...如果执行结果报错,说找不到WMSYS用户,那么执行 @C:\Users\JOYTRAVEL\Desktop\WMSYS用户\owmctab.plb; 再执行owmaggrb和owmaggrs
行转列函数,对于学习sql查询的朋友们,可能会遇到这样的要求,此文档中做了详细记载,希望可以帮到你!
包含owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 三个文件,可解决OracleXE中没有WM_CONCAT函数的问题;解决方案如下:1、下载三个文件:owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 2、用sqlplus登录:...
* 自定义聚合函数 wmsys.wm_concat 替换办法 * 超大字符串拼接,单个字符串4000、分隔符100,可拼出超4000的超长字符串 * 可自定义指定分隔符separator * 可自定义指定排序字段sequence,对于数字或日期类型的...
解决方案:使用Oracle自带的函数 WMSYS.WM_CONCAT,进行拼接。 函数限制:它的输出不能超过4000个字节。 为了不让SQL出错,又可以满足业务的需求,超过4000个字节的部分,使用“。。。” 实现SQL如下: 代码如下: ...
一、Oracle 10G以前使用WMSYS.WM_CONCAT: wmsys.wm_concat将字段的值用”,”来隔开。 select id,wm_concat(name) from tab_name group by id; 二、使用sys_connect_by_path sys_connect_by_path(字段名, 2个字段...
Oracle 重建WMSYS用户的WMSYS.WM_CONCAT函数
Oracle 12c 创建建WMSYS用户的WMSYS.WM_CONCAT函数 解决12c不支持WM_CONCAT函数问题 解压到E:\WMSYS\目录下然后在cmd里执行下边的命令 sqlplus / as sysdba @E:\WMSYS\owmctab.plb; @E:\WMSYS\owmaggrs.plb; @...
Oracle 11g 重建WMSYS用户的WMSYS.WM_CONCAT函数,WMSYS用户