今天才发现了wmsys.wm_concat这个有趣有用的函数,它的作用是以','链接字符。
例子如下:
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;
---------- ------------------------------
6 rows selected
SQL> select id,wmsys.wm_concat(name) name from idtable
---------- --------------------------------------------------------------------------------
SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;
---------- --------------------------------------------------------------------------------
6 rows selected
SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;
---------- --------------------------------------------------------------------------------
6 rows selected
个人觉得这个用法比较有趣.
SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;
---------- --------------------------------------------------------------------------------
6 rows selected
SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;
---------- --------------------------------------------------------------------------------
6 rows selected
转载:http://blog.csdn.net/YY_MM_DD/archive/2008/10/30/3182953.aspx