oracle 列转行

 

 

工作需求：

查询出2017-01-01至2017-12-12时间段内  医疗机构为同济大学附属肺科医院  年龄段在40-55岁人群 的各项费用类型的平均值

select 
ROUND(AVG(M1.YPFY), 2) as 药品费用,
ROUND(AVG(M1.JCFY), 2) as 检查费用,
ROUND(AVG(m1.JYFY), 2) as 检验费用,
ROUND(AVG(M1.SSFY), 2) as 手术费用,
ROUND(AVG(M1.ZLFY), 2) as 治疗费用,
ROUND(AVG(M1.QTFY), 2) as 其他费用  
from CB_ME_YLFY m1,
(select a.SJID ,a.YLJGID,a.FBNLID,a.JZLXID 
from 
CB_ME_FBNL a,
(SELECT FBNLID FROM CB_DI_FBNL) b,
(select SJID from (select SJID, SUBSTR(SJMC,1,4) || '-' || SUBSTR(SJMC,5,6) || '-01' as t from CB_DI_DATE) dat where dat.t >= '2017-01-01' AND dat.t < '2017-12-12') c,
(select YLJGID from CB_DI_YLJG) d ,
(select JZLXID,JZLXMC from CB_DI_JZLX ) e 
where 
a.SJID = c.SJID AND a.FBNLID = b.FBNLID 
AND a.YLJGID = d.YLJGID AND a.JZLXID = e.JZLXID) m2 
WHERE M1.SJID = M2.SJID AND M1.YLJGID = M2.YLJGID AND M1.JZLXID = M2.JZLXID

查询结果：

我要将行转为列变成下面的样式：

　type　　　　　　count

药品费用　　　　4058.63

检查费用                502.81

 

实现：

with ttt as (select ROUND(AVG(M1.YPFY), 2) as 药品费用,ROUND(AVG(M1.JCFY), 2) as 检查费用,ROUND(AVG(m1.JYFY), 2) as 检验费用,
ROUND(AVG(M1.SSFY), 2) as 手术费用,
ROUND(AVG(M1.ZLFY), 2) as 治疗费用,
ROUND(AVG(M1.QTFY), 2) as 其他费用  
from CB_ME_YLFY m1,
(select a.SJID ,a.YLJGID,a.FBNLID,a.JZLXID from CB_ME_FBNL a,(SELECT FBNLID FROM CB_DI_FBNL) b,
(select SJID from (select SJID, SUBSTR(SJMC,1,4) || '-' || SUBSTR(SJMC,5,6) || '-01' as t from CB_DI_DATE) dat 
where dat.t >= '2017-01-01' AND dat.t < '2017-12-12') c,(select YLJGID from CB_DI_YLJG) d ,
(select JZLXID,JZLXMC from CB_DI_JZLX ) e 
where a.SJID = c.SJID AND a.FBNLID = b.FBNLID 
AND a.YLJGID = d.YLJGID AND a.JZLXID = e.JZLXID) m2 
WHERE M1.SJID = M2.SJID AND M1.YLJGID = M2.YLJGID AND M1.JZLXID = M2.JZLXID)

select 费用类型 as type, 金额 as count 
from ttt
unpivot (金额 for 费用类型 in (药品费用, 检查费用, 检验费用, 手术费用,治疗费用,其他费用) )

 

结果：