WM_CONCAT 函數的用法

最新推荐文章于 2024-08-15 14:47:41 发布

iteye_15094

最新推荐文章于 2024-08-15 14:47:41 发布

阅读量149

点赞数

java 同时被 2 个专栏收录

112 篇文章 0 订阅

订阅专栏

WM_CONCAT 函數的用法

1 篇文章 0 订阅

订阅专栏

WM_CONCAT 函數的用法
[size=16px;]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[/size]

例子如下：

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;

[size=16px;] ID NAME 
---------- ------------------------------ 10 ab 10 bc 10 cd 20 hi 20 ij 20 mn[/size]

6 rows selected 
SQL> select id,wmsys.wm_concat(name) name from idtable 
2 group by id;

[size=16px;] ID NAME 
---------- -------------------------------------------------------------------------------- 10 ab,bc,cd 20 hi,ij,mn[/size]

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

[size=16px;] 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[/size]

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

[size=16px;] 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[/size]

6 rows selected

个人觉得这个用法比较有趣.

SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;

[size=16px;] 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[/size]

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;

[size=16px;] ID NAME 
---------- -------------------------------------------------------------------------------- 10 ab 10 bc 10 cd 20 hi 20 ij 20 mn[/size]

6 rows selected

iteye_15094

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
WM_CONCAT 函數的用法

WM_CONCAT 函數的用法[size=16px;]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 WAR...
复制链接

扫一扫

专栏目录