急救！！怎样将可变列转为行？？

wanmeirensheng 2013-08-31 01:54:50
一张病人费用表，含有以下字段：住院号（NO）、费用项目编号（CODE）、费用金额（COST）。
其中每一个病人可能有若干种费用项目，并且费用项目是可重复的。详见下表：

住院号 费用项目编号 费用金额
ZY001 010 30
ZY001 055 60
ZY001 055 40
ZY001 068 50
ZY002 011 30.2
ZY002 047 68
ZY002 055 80
ZY002 019 55.5
ZY002 019 63

现在要求统计出每个病人的每一项费用的金额汇总
以如下方式输出：

住院号 010 055 068 ……
ZY001 30 100 50 ……
ZY002 0 80 0 ……

...全文
292 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
dyccsxg 2013-09-01
  • 打赏
  • 举报
 回复
生成 select 语句的 pl/sql 块如下： 

set serveroutput on
declare 
  cursor c1 is select distinct code from t1 order by code;
  v_sql varchar2(1024);
begin
  v_sql := 'select '||chr(10)||'no,'||chr(10);
  for c1_result in c1 loop
    v_sql := v_sql||'sum(case code when '''||c1_result.code||''' then cost else 0 end) c'||c1_result.code||','||chr(10);
  end loop;
  v_sql := substr(v_sql, 0, length(v_sql) - 2);
  v_sql := v_sql||chr(10)||'from t1 group by no'||chr(10)||'order by no;';
  dbms_output.put_line(v_sql);
end;
/
dyccsxg 2013-09-01
  • 打赏
  • 举报
 回复 

create table t1(no varchar2(10), code varchar(5), cost float);
insert into t1(no, code, cost) values('ZY001', '010', 30);
insert into t1(no, code, cost) values('ZY001', '055', 60);
insert into t1(no, code, cost) values('ZY001', '055', 40);
insert into t1(no, code, cost) values('ZY001', '068', 50);
insert into t1(no, code, cost) values('ZY002', '011', 30.2);
insert into t1(no, code, cost) values('ZY002', '047', 68);
insert into t1(no, code, cost) values('ZY002', '055', 80);
insert into t1(no, code, cost) values('ZY002', '019', 55.5);
insert into t1(no, code, cost) values('ZY002', '019', 63);
select 
  no 住院号, 
  sum(case code when '010' then cost else 0 end) C010,
  sum(case code when '055' then cost else 0 end) C055,
  sum(case code when '068' then cost else 0 end) C068,
  sum(case code when '011' then cost else 0 end) C011,
  sum(case code when '047' then cost else 0 end) C047,
  sum(case code when '019' then cost else 0 end) C019
from t1
group by no
order by no;
super007007007 2013-08-31
  • 打赏
  • 举报
 回复 
WITH T(H_NO,ITEM_NO,TOTAL_FEE) AS (
SELECT 'ZY001'  ,'001'          ,1           FROM DUAL UNION 
SELECT 'ZY001'  ,'002'          ,3           FROM DUAL UNION 
SELECT 'ZY001'  ,'003'          ,5           FROM DUAL UNION 
SELECT 'ZY001'  ,'004'          ,7           FROM DUAL UNION 
SELECT 'ZY001'  ,'005'          ,9           FROM DUAL UNION 
SELECT 'ZY001'  ,'006'          ,11          FROM DUAL UNION 
SELECT 'ZY001'  ,'007'          ,13          FROM DUAL UNION 
SELECT 'ZY001'  ,'008'          ,15          FROM DUAL UNION 
SELECT 'ZY001'  ,'009'          ,17          FROM DUAL UNION 
SELECT 'ZY001'  ,'010'          ,19          FROM DUAL UNION 
SELECT 'ZY001'  ,'011'          ,21          FROM DUAL UNION 
SELECT 'ZY001'  ,'012'          ,23          FROM DUAL UNION 
SELECT 'ZY001'  ,'013'          ,25          FROM DUAL UNION 
SELECT 'ZY001'  ,'014'          ,27          FROM DUAL UNION 
SELECT 'ZY001'  ,'015'          ,29          FROM DUAL UNION 
SELECT 'ZY001'  ,'016'          ,31          FROM DUAL UNION 
SELECT 'ZY001'  ,'017'          ,33          FROM DUAL UNION 
SELECT 'ZY001'  ,'018'          ,35          FROM DUAL UNION 
SELECT 'ZY001'  ,'019'          ,37          FROM DUAL UNION 
SELECT 'ZY001'  ,'020'          ,39          FROM DUAL UNION 
SELECT 'ZY001'  ,'021'          ,41          FROM DUAL UNION 
SELECT 'ZY001'  ,'022'          ,43          FROM DUAL UNION 
SELECT 'ZY001'  ,'023'          ,45          FROM DUAL UNION 
SELECT 'ZY001'  ,'024'          ,47          FROM DUAL UNION 
SELECT 'ZY001'  ,'025'          ,49          FROM DUAL UNION 
SELECT 'ZY001'  ,'026'          ,51          FROM DUAL UNION 
SELECT 'ZY001'  ,'027'          ,53          FROM DUAL UNION 
SELECT 'ZY001'  ,'028'          ,55          FROM DUAL UNION 
SELECT 'ZY001'  ,'029'          ,57          FROM DUAL UNION 
SELECT 'ZY001'  ,'030'          ,59          FROM DUAL UNION 
SELECT 'ZY001'  ,'031'          ,61          FROM DUAL UNION 
SELECT 'ZY001'  ,'032'          ,63          FROM DUAL UNION 
SELECT 'ZY001'  ,'033'          ,65          FROM DUAL UNION 
SELECT 'ZY001'  ,'034'          ,67          FROM DUAL UNION 
SELECT 'ZY001'  ,'035'          ,69          FROM DUAL UNION 
SELECT 'ZY001'  ,'036'          ,71          FROM DUAL UNION 
SELECT 'ZY001'  ,'037'          ,73          FROM DUAL UNION 
SELECT 'ZY001'  ,'038'          ,75          FROM DUAL UNION 
SELECT 'ZY001'  ,'039'          ,77          FROM DUAL UNION 
SELECT 'ZY001'  ,'040'          ,79          FROM DUAL UNION 
SELECT 'ZY001'  ,'041'          ,81          FROM DUAL UNION 
SELECT 'ZY001'  ,'042'          ,83          FROM DUAL UNION 
SELECT 'ZY001'  ,'043'          ,85          FROM DUAL UNION 
SELECT 'ZY001'  ,'044'          ,87          FROM DUAL UNION 
SELECT 'ZY001'  ,'045'          ,89          FROM DUAL UNION 
SELECT 'ZY001'  ,'046'          ,91          FROM DUAL UNION 
SELECT 'ZY001'  ,'047'          ,93          FROM DUAL UNION 
SELECT 'ZY001'  ,'048'          ,95          FROM DUAL UNION 
SELECT 'ZY001'  ,'049'          ,97          FROM DUAL UNION 
SELECT 'ZY001'  ,'050'          ,99          FROM DUAL UNION 
SELECT 'ZY001'  ,'051'          ,101         FROM DUAL UNION 
SELECT 'ZY001'  ,'052'          ,103         FROM DUAL UNION 
SELECT 'ZY001'  ,'053'          ,105         FROM DUAL UNION 
SELECT 'ZY001'  ,'054'          ,107         FROM DUAL UNION 
SELECT 'ZY001'  ,'055'          ,109         FROM DUAL UNION 
SELECT 'ZY001'  ,'056'          ,111         FROM DUAL UNION 
SELECT 'ZY001'  ,'057'          ,113         FROM DUAL UNION 
SELECT 'ZY001'  ,'058'          ,115         FROM DUAL UNION 
SELECT 'ZY001'  ,'059'          ,117         FROM DUAL UNION 
SELECT 'ZY001'  ,'060'          ,119         FROM DUAL UNION 
SELECT 'ZY001'  ,'061'          ,121         FROM DUAL UNION 
SELECT 'ZY001'  ,'062'          ,123         FROM DUAL UNION 
SELECT 'ZY001'  ,'063'          ,125         FROM DUAL UNION 
SELECT 'ZY001'  ,'064'          ,127         FROM DUAL UNION 
SELECT 'ZY001'  ,'065'          ,129         FROM DUAL UNION 
SELECT 'ZY001'  ,'066'          ,131         FROM DUAL UNION 
SELECT 'ZY001'  ,'067'          ,133         FROM DUAL UNION 
SELECT 'ZY001'  ,'068'          ,135         FROM DUAL UNION 
SELECT 'ZY001'  ,'069'          ,137         FROM DUAL UNION 
SELECT 'ZY002'  ,'011'          ,30.2       FROM DUAL UNION 
SELECT 'ZY002'  ,'047'          ,68         FROM DUAL UNION 
SELECT 'ZY002'  ,'055'          ,80         FROM DUAL UNION 
SELECT 'ZY002'  ,'019'          ,55.5       FROM DUAL UNION 
SELECT 'ZY002'  ,'019'          ,63         FROM DUAL 
)
, T1(H_NO,ITEM_NO,TOTAL_FEE)  AS (SELECT H_NO,ITEM_NO,SUM(TOTAL_FEE) FROM T GROUP BY H_NO,ITEM_NO
)
,ITEM(ID,ITEM_NO) 
AS 
(
 SELECT ROWNUM,LPAD(ROWNUM,3,'0') FROM dual connect by level <= 69
)
SELECT 
   A.H_NO  
  ,SUM(DECODE(B.ID,1, A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,2 ,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,3 ,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,4 ,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,5 ,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,6 ,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,7 ,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,8 ,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,9 ,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,10,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,11,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,12,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,13,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,14,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,15,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,16,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,17,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,18,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,19,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,20,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,21,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,22,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,23,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,24,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,25,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,26,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,27,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,28,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,29,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,30,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,31,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,32,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,33,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,34,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,35,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,36,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,37,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,38,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,39,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,40,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,41,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,42,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,43,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,44,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,45,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,46,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,47,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,48,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,49,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,50,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,51,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,52,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,53,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,54,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,55,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,56,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,57,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,58,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,59,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,60,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,61,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,62,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,63,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,64,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,65,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,66,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,67,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,68,A.TOTAL_FEE))
  ,SUM(DECODE(B.ID,69,A.TOTAL_FEE))
 FROM T1 A
 INNER JOIN ITEM B
  ON A.ITEM_NO = B.ITEM_NO
GROUP BY 
   A.H_NO  
 order by 1
Rotel-刘志东 2013-08-31
  • 打赏
  • 举报
 回复
DECODE()函数
不会消失的夜晚 2013-08-31
  • 打赏
  • 举报
 回复
引用 3 楼 wanmeirensheng 的回复:
[quote=引用 2 楼 xxhhbb1538 的回复:] 如果费用项目编号很多，而且是动态增加的，则行转列是不现实的
感谢回复！ 费用项目总共69种 这样是不可实现的么？[/quote] 我做过的行转列一般都是列数较少的，你这六七十种，效率是不是很慢啊，而且，你这个编号以后还会变吗，增加或删除或修改。
wanmeirensheng 2013-08-31
  • 打赏
  • 举报
 回复
引用 2 楼 xxhhbb1538 的回复:
如果费用项目编号很多，而且是动态增加的，则行转列是不现实的
感谢回复！ 费用项目总共69种 这样是不可实现的么？
不会消失的夜晚 2013-08-31
  • 打赏
  • 举报
 回复
如果费用项目编号很多，而且是动态增加的，则行转列是不现实的
wanmeirensheng 2013-08-31
  • 打赏
  • 举报
 回复
原表样式：


处理结果：

怎么将SD卡RAW格式转换为FAT32格式？
如何将SD卡RAW格式转换为FAT32格式？在本文中您可以找到答案！
大学生急救新思维与户外模拟训练知识总结 2018年秋
世界急救日：9月8日 现代复苏三大里程碑： 体外电除颤 口对口人工呼吸 胸外按压 临床死亡：微弱的新陈代谢，可以被逆转 生物学死亡：新陈代谢终止，不可逆转 心跳骤停的现场判断： 意识丧失 瞳孔散大 皮肤口唇...
把U盘FAT32转换成NTFS有什么坏处
因此本文就mac读写ntfs软件，ntfs怎么在mac使用等问题来展开讨论与回答，感兴趣的朋友不妨往下看。 一、mac读写ntfs软件 1、目前Mac系统支持读写NTFS磁盘格式的软件有很多，比如Tuxera NTFS Fo
从挂死的Chrome中抢救未提交的图文
使用内存搜索命令列出这个模块的所有可读字符： s -sa 0007ffe`1af20000 00007ffe`1b01e000 得到的信息量很大，暂时不想细读。 继续看栈帧01，是邪恶的Sleep，老雷焦急万分，这里却在调用Sleep睡觉，真是糟糕的软件...
我在CSDN参与的3000个帖子
一个简单的算法问题，怎么样在一个数组里获得4个不同的数字 29： [急急急100分求教]高手进来瞧瞧，请教算法问题！ 30： 【问】如何查找Zip包内部文件内容？ 31： 回答问题被抗议，散分 32： ...
Oracle

17,090

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章