oracle列传行

oracle列传行

wm_concat函数

注意: wm_concat有长度限制或者版本不支持,11.1以后不支持
WM_CONCAT(XXX)
TO_CHAR(WM_CONCAT(XXX))
解释：通过GROUP BY聚合函数，将一个组的结果拼接起来

listagg函数

LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
解释：通过GROUP BY聚合函数 将一个组的结果拼接起来

实战

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