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