listagg wm_concat 行转列

一、

这个写法和wm_concat相似,
listagg(day,',')要把哪一列转换为同一行
within group (order by day)同一行如何排序

with temp as
 (
 select '1月' month, '1' day from dual union all
 select '2月' month, '1' day from dual union all
 select '2月' month, '2' day from dual union all
 select '3月' month, '1' day from dual union all
 select '3月' month, '2' day from dual union all
 select '3月' month, '3' day from dual 
 )
select 
 month,listagg(day,',')within group (order by day ) days
 from temp 
 group by month;

 

with temp as
 (
 select '1月' month, '1' day from dual union all
 select '2月' month, '1' day from dual union all
 select '2月' month, '2' day from dual union all
 select '3月' month, '1' day from dual union all
 select '3月' month, '2' day from dual union all
 select '3月' month, '3' day from dual 
 )
select 
   month,
   to_char(wm_concat(day))
 from temp 
group by month;

二、

不使用group by

with temp as
 (
 select '1月' month, '1' day from dual union all
 select '2月' month, '1' day from dual union all
 select '2月' month, '2' day from dual union all
 select '3月' month, '1' day from dual union all
 select '3月' month, '2' day from dual union all
 select '3月' month, '3' day from dual 
 )
select month , 
listagg(day,',')within group (order by day) over (partition by month) DAYS
from temp

 

with temp as
 (
 select '1月' month, '1' day from dual union all
 select '2月' month, '1' day from dual union all
 select '2月' month, '2' day from dual union all
 select '3月' month, '1' day from dual union all
 select '3月' month, '2' day from dual union all
 select '3月' month, '3' day from dual 
 )
select *
  from (select month,
               to_char(wm_concat(day) over(partition by month order by day)) days,
               row_number() over(partition by month order by day desc) rn
          from temp)
 where rn = 1

 

转载于:https://www.cnblogs.com/-beauTiFul/p/8663575.html