—–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 ( 苹果，雪梨，香蕉，龙眼
)
)
原文地址:https://blog.csdn.net/prodigywunder/article/details/134722603
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.7code.cn/show_20562.html
如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！
