Oracle中行转列（pivot）函数解析（二）

本文主要是介绍Oracle中行转列（pivot）函数解析（二），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

Oracle行转列就是把某一个字段的值作为唯一值，然后另外一个字段的行值转换成它的列值。

案例原始数据如下：

 方法一：利用group by实现

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_id

 方法二：Oracle11g之后提供了自带函数PIVOT可以完美解决这个行转列的需求，具体语法结构如下：

select *
   from (数据查询集)
 pivot
 (
        sum(xmje /*行转列后 列的值*/
        for xmdm /*需要行转列的列*/
        in(转换后列的值))
 )

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))

 参考博客：https://blog.csdn.net/langweixiana/article/details/116011508

这篇关于Oracle中行转列（pivot）函数解析（二）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！