Oracle行转列，列转行使用实例

 -----1.行转换为列

select a.fworkcenter as 车间,F1||'-数量' as 类型,  fspec as 规格  ,ftype as 前缀
        , to_char(fdate,'YYYY-MM-dd') as 日期,  (case when a.fcode in (900,901) then to_char(fcount,'fm90.990') else  cast(fcount as varchar(20)) end)  值   ,
          a.fname,a.f2 as 厚度
         from   avg_resistance a
 where 1=1 and  a.fdate>=:STARTDATE and a.fdate<=:ENDDATE and length(a.ftype)=5 )t --and a.f1='天' 
      
  pivot  (    max( 值 )      ----随便给个函数即可
        
        for fname in 
              (
     '合计' as "合计",
'电阻(<0.2)' as "电阻(小于0。2)",
'电阻(0.2,0.3)' as "电阻(0。2,0。3)",
'电阻(0.3,0.4)' as "电阻(0。3,0。4)",
'均值' as "均值" 

    ) )
        where 1=1  ；   

 -----2.列行转换
 select  *  
 from (  select a.containername, 
(case when nvl(a.苹果,0)>0 then 苹果else 0 end )   as 苹果
(case when nvl(a.雪梨,0)>0 then 雪梨 else 0 end )   as 雪梨
(case when nvl(a.香蕉,0)>0 then 香蕉  else 0 end )   as 香蕉,
(case when nvl(a.龙眼,0)>0 then 龙眼  else 0 end )   as 龙眼，
a.cuworkorder 
from cucontainergraderrecord a 
where a.recordtime>=to_date('2023-10-01','yyyy-mm-dd')
and a.containername like 'Z%'
and a.specname='分选')t
 
unpivot 
(
  fcount    ---(原表的列对应的值) 
  
   for fitem   ----(原表列名称，多少列，就变多少行, oracle的直接用列名称，不用’，''等) 
  
   in (  苹果，雪梨，香蕉，龙眼
  )
  
  )