Oracle函数wmsys.wm_concat的使用

文章转载http://www.2cto.com/database/201204/128928.html

 

首先介绍语法：

wmsys.wm_concat

 

Definition:

The Oracle PL/SQL WM_CONCAT function is used to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. In effect, it cross-tabulates a comma delimited list.

 

Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems. The LISTAGG function, which can produce the same output asWM_CONCAT is both documented and supported by Oracle.

 

例子：

[sql]

select t1.main_id,

to_char(wmsys.wm_concat(t1.send_id || '||' || t2.realname || '||' ||

t2.IMG_PATH)) sendidlist

from LCMS_SEND_USER t1, LCMS_USER_STUD t2

group by t1.main_id;

 

结果：

 

 

网络中也有这样使用：

 

下面只是收藏：

 

[sql]

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

www.2cto.com

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]

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

www.2cto.com

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;

www.2cto.com

ID NAME

---------- --------------------------------------------------------------------------------

10 ab

10 bc

10 cd

20 hi

20 ij

20 mn

 

6 rows selected

 

 

 

作者 psyuhen