Oracle中行转列(pivot)函数解析(二)
2022/9/5 2:22:57
本文主要是介绍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_idView 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
这篇关于Oracle中行转列(pivot)函数解析(二)的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-13PingCAP 戴涛:构建面向未来的金融核心系统
- 2024-05-09flutter3.x_macos桌面os实战
- 2024-05-09Rust中的并发性:Sync 和 Send Traits
- 2024-05-08使用Ollama和OpenWebUI在CPU上玩转Meta Llama3-8B
- 2024-05-08完工标准(DoD)与验收条件(AC)究竟有什么不同?
- 2024-05-084万 star 的 NocoDB 在 sealos 上一键起,轻松把数据库编程智能表格
- 2024-05-08Mac 版Stable Diffusion WebUI的安装
- 2024-05-08解锁CodeGeeX智能问答中3项独有的隐藏技能
- 2024-05-08RAG算法优化+新增代码仓库支持,CodeGeeX的@repo功能效果提升
- 2024-05-08代码报错不用愁,CodeGeeX一键完成代码修复、错误解释的功能上线了!