oracle 行转列函数pivot

1、原数据sql

select '2023_05' as yf,'类型1' as mc,'12.1' as dj from dual
      union
      select '2023_06' as yf,'类型1' as mc,'11.7' as dj from dual
      union
      select '2023_07' as yf,'类型1' as mc,'11.6' as dj from dual
      union
      select '2023_05' as yf,'类型2' as mc,'19' as dj from dual
      union
      select '2023_06' as yf,'类型2' as mc,'18.26' as dj from dual
      union
      select '2023_07' as yf,'类型2' as mc,'17.87' as dj from dual
      union
      select '2023_05' as yf,'类型3' as mc,'25.63' as dj from dual

原数据展示：

 

2、使用pivot函数进行行转列

select mc,NVL(yf_2023_05,0) as yf_2023_05,NVL(yf_2023_06,0) as yf_2023_06,NVL(yf_2023_07,0) as yf_2023_07 from 
(with temp as (
      select '2023_05' as yf,'类型1' as mc,'12.1' as dj from dual
      union
      select '2023_06' as yf,'类型1' as mc,'11.7' as dj from dual
      union
      select '2023_07' as yf,'类型1' as mc,'11.6' as dj from dual
      union
      select '2023_05' as yf,'类型2' as mc,'19' as dj from dual
      union
      select '2023_06' as yf,'类型2' as mc,'18.26' as dj from dual
      union
      select '2023_07' as yf,'类型2' as mc,'17.87' as dj from dual
      union
      select '2023_05' as yf,'类型3' as mc,'25.63' as dj from dual
)
select * from (select mc,yf,dj from temp)
pivot (sum(dj) for yf in('2023_05' as yf_2023_05,'2023_06' as yf_2023_06,'2023_07' as yf_2023_07))
) order by mc;

行转列后数据展示：