一张病人费用表,含有以下字段:住院号(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 ……列转行
其中每一个病人可能有若干种费用项目,并且费用项目是可重复的。详见下表:住院号 费用项目编号 费用金额
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 ……列转行
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
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;
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;
/