Oracle行转列就是把某一个字段的值作为唯一值,然后另外一个字段的行值转换成它的列值。
案例原始数据如下:
方法一:利用group by实现
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select t.mr_sl_id, sum(decode(xmdm, '01', t.xmje, 0)) 床位费, sum(decode(xmdm, '02', t.xmje, 0)) 诊察费, sum(decode(xmdm, '03', t.xmje, 0)) 检查费, sum(decode(xmdm, '04', t.xmje, 0)) 化验费, sum(decode(xmdm, '05', t.xmje, 0)) 治疗费, sum(decode(xmdm, '06', t.xmje, 0)) 手术费, sum(decode(xmdm, '07', t.xmje, 0)) 护理费, sum(decode(xmdm, '08', t.xmje, 0)) 卫生材料费, sum(decode(xmdm, '09', t.xmje, 0)) 西药费, sum(decode(xmdm, '10', t.xmje, 0)) 中药饮片费, sum(decode(xmdm, '11', t.xmje, 0)) 中成药费, sum(decode(xmdm, '12', t.xmje, 0)) 一般诊疗费, sum(decode(xmdm, '13', t.xmje, 0)) 挂号费, sum(decode(xmdm, '14', t.xmje, 0)) 其他费 from hos_drgs.sl_charge t where t.mr_sl_id = 'ZY110000557222' group by t. mr_sl_idView Code
方法二:Oracle11g之后提供了自带函数PIVOT可以完美解决这个行转列的需求,具体语法结构如下:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from (数据查询集) pivot ( sum(xmje /*行转列后 列的值*/ for xmdm /*需要行转列的列*/ in(转换后列的值)) )View Code
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from (select mr_sl_id, xmdm, sum(xmje) fee from hos_drgs.sl_charge where mr_sl_id = 'ZY110000557222' group by mr_sl_id, xmdm) pivot(sum(fee) for xmdm in('01' as bed_fee, '02' as consultation_fee, '03' as inspection_fee, '04' as assay_fee, '05' as treatment_fee, '06' as operation_fee, '07' as nursing_fee, '08' as sanitary_material_fee, '09' as western_medicine_fee, '10' as cn_medicine_pieces_fee, '11' as cn_patent_medicine_fee, '12' as general_medical_fee, '13' as regist_fee, '14' as other_fee))View Code
参考博客:https://blog.csdn.net/langweixiana/article/details/116011508
标签:fee,xmdm,sum,xmje,decode,转列,sl,Oracle,pivot From: https://www.cnblogs.com/daytoy105/p/16655746.html