listagg

with temp as(  

  select 'China' nation ,'Guangzhou' city from dual union all  

  select 'China' nation ,'Shanghai' city from dual union all  

  select 'China' nation ,'Beijing' city from dual union all  

  select 'USA' nation ,'New York' city from dual union all  

  select 'USA' nation ,'Bostom' city from dual union all  

  select 'Japan' nation ,'Tokyo' city from dual   

)  

select nation,listagg(city,',') within GROUP (order by city)  

from temp  

group by nation ;

这是最基础的用法：

LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

第一个xxx是用来拼接的字段，

第二个xxx既是用来分割拼接字段的字符串，

第三个xxx是用来给拼接的字符一个排序。

 

用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。

非常方便。

 

同样是聚合函数，还有一个高级用法 over(partition by XXX)，在不使用Group by语句时候，也可以使用LISTAGG函数：

with temp as(  

  select 500 population, 'China' nation ,'Guangzhou' city from dual union all  

  select 1500 population, 'China' nation ,'Shanghai' city from dual union all  

  select 500 population, 'China' nation ,'Beijing' city from dual union all  

  select 1000 population, 'USA' nation ,'New York' city from dual union all  

  select 500 population, 'USA' nation ,'Bostom' city from dual union all  

  select 500 population, 'Japan' nation ,'Tokyo' city from dual   

)  

select population,  

       nation,  

       city,  

       listagg(city,',') within GROUP (order by city) over (partition by nation) rank  

from temp  

 

承接上面

over 之后的partition by 是给listagg分组的，相当于上面的group by，只是这里的返回结果与group by不同，group by是分组后的合并，这里只是将拼接的字段分组合并进行列转行。

总结，这种方式是oracle数据库11g及以后提供的函数，比较方便，但是缺点是返回值是varchar2类型，如果拼接的字符串超过4000那么将报 ora-01489:字符串连接的结果过长 的错误，需要使用    wmsys.wm_concat 函数，且最好自定义 wmsys.wm_concat函数。

原文：http://blog.chinaunix.net/uid-23622436-id-4420076.html