oracle 列轉行 LISTAGG和WM_CONCAT

WMSYS.WM_CONCAT: 依賴WMSYS 使用者，不同oracle環境時可能用不了，傳回型別為CLOB，可用substr截取長度後to_char轉化為字元類型

LISTAGG  : 11g2才提供的函數，不支援distinct,拼接長度不能大於4000,函數返回為varchar2類型，最大長度為4000.

 

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 nation)   citylist
from temp 
group by nation ;

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