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

标签：fee xmdm sum xmje decode 转列 sl 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

View Code

 

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

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

View Code

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