行转列

最新推荐文章于 2023-12-21 12:29:06 发布

xlj3

最新推荐文章于 2023-12-21 12:29:06 发布

阅读量514

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/luman1991/article/details/65452382

版权

数据库专栏收录该内容

61 篇文章 0 订阅

订阅专栏

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, wm_concat(city)
from temp  
group by nation ;


-------------------------
--这是最基础的用法：
--LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
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 
-------------------------

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