03-012 oracle中xmlagg和listagg

oracle列转换行有如下方法

一、wm_concat

WMSYS.WM_CONCAT: 依赖WMSYS 用户，不同oracle环境时可能用不了，返回类型为CLOB，可用substr截取长度后to_char转化为字符类型
1、语法

wm_concat()

实例如下

select wm_concat(distinct vendor) from ams_asset_info 

二、XMLAGG函数

效果和wm_concat一样，但是性能不一样，XMLAGG排序一下，性能会快一些
1、语法

1.1、xmlagg(xmlparse(content(合并字段)||’,’ wellformed) order by 排序字段).getclobval()
1.2、XMLAGG(XMLELEMENT(e, 合并字段, ‘,’).extract(’//text()’))

//1.1

with temp as(  
select 'China' nation ,'HeNan' 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 'India' nation ,'New Delhi' city from dual   
)  
select XMLAGG(xmlparse(content(city)||',' wellformed) order by nation).getclobval() as city
from temp  
group by nation

//1.2

with temp as(  
select 'China' nation ,'HeNan' 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 'India' nation ,'New Delhi' city from dual   
)  
select XMLAGG(XMLELEMENT(e,city,',').extract('//text()')).getClobVal() as Cities
from temp  
group by nation

oracle列转行函数还有listagg，也可以用sys_connect_by_path来做，具体根据需求来

三、listagg

LISTAGG : 11g2才提供的函数，不支持distinct,拼接长度不能大于4000,函数返回为varchar2类型，最大长度为4000。当查询较慢时，使用listagg代替wm_concat
1、语法

LISTAGG( [,]) WITHIN GROUP (ORDER BY ) [OVER (PARTITION BY )]

//1.1
LISTAGG( [,]) WITHIN GROUP (ORDER BY )

with temp as(  
select 'China' nation ,'HeNan' 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 'India' nation ,'New Delhi' city from dual   
)  
select 
listagg(city,',') within GROUP (order by city)  as Cities
from temp  

//1.2
LISTAGG( [,]) WITHIN GROUP (ORDER BY ) OVER (PARTITION BY )

with temp as(  
select 'China' nation ,'HeNan' 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 'India' nation ,'New Delhi' city from dual   
)  
select 
distinct nation,
listagg(city,',') within GROUP (order by city) over(partition by nation)  as Cities
from temp