wm_concat和listagg函数测试

关于这个帖子提到的两个函数 http://www.itpub.net/thread-1717610-1-1.html

 

测试环境redhat linux with oracle 11g

 

SQL> create table a as select 1 id,'x' t1 from dual union all select 2,'y' from dual;

Table created.

SQL> create table b as select 1 id,'a' t2 from dual
  2  union all select 1,'b' from dual
  3  union all select 1,'c' from dual
  4  union all select 2,'d' from dual
  5  union all select 2,'e' from dual;

Table created.

SQL>  select a.id,a.t1,wm_concat(b.t2) list from a,b where a.id=b.id group by a.id,a.t1;

        ID T1
---------- ---
LIST
------------------------------
         1 x
a,c,b

         2 y
d,e

SQL> select a.id,a.t1,listagg(b.t2,',') within group(order by a.id,a.t1) from a,b where a.id=b.id group by a.id,a.t1;

        ID T1
---------- ---
LISTAGG(B.T2,',')WITHINGROUP(O
------------------------------
         1 x
a,b,c

         2 y
d,e

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/2281175/viewspace-746085/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/2281175/viewspace-746085/