Oracle-數據實現豎排打印

--存放重證評分的數據表
create table ZZPFapache2
(
? ZZ_datetime DATE,?? --時間
? ZZ_zongfen INTEGER, --總分
? ZZ_shiwanglui INTEGER, --死亡率?
? ZZ_BINGRENID VARCHAR2(50), --病人ID
? ZZ_h1f1 INTEGER,? --第1行1個分
? ZZ_h1m1 VARCHAR2(40),
? ZZ_h1f2 INTEGER,? --第1行2個分
? ZZ_h1m2 VARCHAR2(40),
? ZZ_h2f1 INTEGER,? --第2行1個分
? ZZ_h2m1 VARCHAR2(40),
? ZZ_h2f2 INTEGER,? --第2行2個分
? ZZ_h2m2 VARCHAR2(40),
? ZZ_h2f3 INTEGER,? --第2行3個分
? ZZ_h2m3 VARCHAR2(40),
? ZZ_h3f1 INTEGER,? --第3行1個分
? ZZ_h3m1 VARCHAR2(40),
? ZZ_h3f2 INTEGER,? --第3行2個分
? ZZ_h3m2 VARCHAR2(40),
? ZZ_h3f3 INTEGER,? --第3行3個分
? ZZ_h3m3 VARCHAR2(40),
? ZZ_h4f1 INTEGER,? --第4行1個分
? ZZ_h4m1 VARCHAR2(40),
? ZZ_h4f2 INTEGER,? --第4行2個分
? ZZ_h4m2 VARCHAR2(40),
? ZZ_h4f3 INTEGER,? --第4行3個分
? ZZ_h4m3 VARCHAR2(40),
? ZZ_h5f1 INTEGER,? --第5行1個分
? ZZ_h5m1 VARCHAR2(40),
? ZZ_h5f2 INTEGER,? --第5行2個分
? ZZ_h5m2 VARCHAR2(40),
? ZZ_h5f3 INTEGER,? --第5行3個分
? ZZ_h5m3 VARCHAR2(40),
? ZZ_h6f1 INTEGER,? --第6行1個分
? ZZ_h6m1 VARCHAR2(40),
? ZZ_h6f2 INTEGER,? --第6行2個分
? ZZ_h6m2 VARCHAR2(40),
? ZZ_h7f1 INTEGER,? --第7行1個分
? ZZ_h7m1 VARCHAR2(40),
? ZZ_h7f2 INTEGER,? --第6行2個分
? ZZ_h7m2 VARCHAR2(40),
? ZZ_h8f1 INTEGER,? --第8行1個分
? ZZ_h8m1 VARCHAR2(40),
? ZZ_h8f2 INTEGER,? --第8行2個分
? ZZ_h8m2 VARCHAR2(40),
? ZZ_f1 INTEGER,? --復選第1個分
? ZZ_f2 INTEGER,? --復選第2個分
? ZZ_f3 INTEGER,? --復選第3個分
? ZZ_f4 INTEGER,? --復選第4個分
? ZZ_f5 INTEGER,? --復選第5個分
? ZZ_GUserID VARCHAR2(40),
? ZZ_GUserName VARCHAR2(40)
)
tablespace TS_IMIS
? pctfree 10
? initrans 1
? maxtrans 255
? storage
? (
??? initial 64K
??? minextents 1
??? maxextents unlimited
? );

--存放重癥評分豎排數據視圖
create or replace view zzpfdy as
select "ZZ_BINGRENID","ZZ_DATETIME","SUBJECT1","RESULT" from
(
? select 1 line,ZZ_BINGRENID,ZZ_DATETIME ,? '得分' SUBJECT1, ZZ_ZONGFEN result from zzpfapache2
? union all
? select 2 line,ZZ_BINGRENID,ZZ_DATETIME ,? '死亡率'SUBJECT1, ZZ_SHIWANGLUI result from zzpfapache2
? union all
? select 3 line,ZZ_BINGRENID,ZZ_DATETIME ,? '年齡'SUBJECT1, ZZ_H1F1 result from zzpfapache2
??? union all
? select 4 line,ZZ_BINGRENID,ZZ_DATETIME ,? '有嚴重慢性器官系統功能不全'SUBJECT1,ZZ_H1F2 result from zzpfapache2
??? union all
? select 5 line,ZZ_BINGRENID,ZZ_DATETIME ,? '睜眼反應' SUBJECT1 ,ZZ_H2F1 result from zzpfapache2
??? union all
? select 6 line,ZZ_BINGRENID,ZZ_DATETIME ,? '言語反應' SUBJECT1,ZZ_H2F2 result from zzpfapache2
??? union all
? select 7 line,ZZ_BINGRENID,ZZ_DATETIME ,? '運動反應' SUBJECT1,ZZ_H2F3 result from zzpfapache2
??? union all
? select 8 line,ZZ_BINGRENID,ZZ_DATETIME ,? '體溫'SUBJECT1,ZZ_H3F1 result from zzpfapache2
??? union all
? select 9 line,ZZ_BINGRENID,ZZ_DATETIME ,? '平均血壓（mmHg）'SUBJECT1, ZZ_H3F2 result from zzpfapache2
??? union all
? select 10 line,ZZ_BINGRENID,ZZ_DATETIME , '心率（次/分）'SUBJECT1 , ZZ_H3F3 result from zzpfapache2
??? union all
? select 11 line,ZZ_BINGRENID,ZZ_DATETIME , '呼吸（次/分）'SUBJECT1 ,ZZ_H4F1 result from zzpfapache2
????? union all
? select 12 line,ZZ_BINGRENID,ZZ_DATETIME , 'FIO2' SUBJECT1,ZZ_H4F2 result from zzpfapache2
??? union all
?select 13 line,ZZ_BINGRENID,ZZ_DATETIME ,? '血清HCO3（無ABGs）（mmol/L）' SUBJECT1,ZZ_H4F3 result from zzpfapache2
?? union all
? select 14 line,ZZ_BINGRENID,ZZ_DATETIME , '血清Na（mmol/L）' SUBJECT1,ZZ_H5F1 result from zzpfapache2
??? union all
? select 15 line,ZZ_BINGRENID,ZZ_DATETIME , '血清K（mmol/L）' SUBJECT1,ZZ_H5F2 result from zzpfapache2
??? union all
? select 16 line,ZZ_BINGRENID,ZZ_DATETIME , '動脈血PH' SUBJECT1 ,ZZ_H5F3 result from zzpfapache2
??? union all
? select 17 line,ZZ_BINGRENID,ZZ_DATETIME , '血細胞比容（%）' SUBJECT1,ZZ_H6F1 result from zzpfapache2
???? union all
? select 18 line,ZZ_BINGRENID,ZZ_DATETIME , 'WBC（*1000/mm3）' SUBJECT1,ZZ_H6F2 result from zzpfapache2
???? union all
? select 19 line,ZZ_BINGRENID,ZZ_DATETIME , 'Pa02（mmHg...KPa）' SUBJECT1,ZZ_H7F1 result from zzpfapache2
???? union all
? select 20 line,ZZ_BINGRENID,ZZ_DATETIME , 'A-aD02（mmHg...KPa）' SUBJECT1,ZZ_H7F2 result from zzpfapache2
??? union all
? select 21 line,ZZ_BINGRENID,ZZ_DATETIME , '血清肌酐（mg/dl..micromol/l）' SUBJECT1,ZZ_H8F1 result from zzpfapache2
??? union all
? select 22 line,ZZ_BINGRENID,ZZ_DATETIME , '急性腎功能衰竭' SUBJECT1,ZZ_H8F2 result from zzpfapache2
??? union all
? select 23 line,ZZ_BINGRENID,ZZ_DATETIME , '有無功能不全免疫損害:肝' SUBJECT1,ZZ_F1 result from zzpfapache2
??? union all
? select 24 line,ZZ_BINGRENID,ZZ_DATETIME , '有無功能不全免疫損害:心血管' SUBJECT1,ZZ_F2 result from zzpfapache2
??? union all
? select 25 line,ZZ_BINGRENID,ZZ_DATETIME , '有無功能不全免疫損害:呼吸系統' SUBJECT1,ZZ_F3 result from zzpfapache2
??? union all
? select 26 line,ZZ_BINGRENID,ZZ_DATETIME , '有無功能不全免疫損害:腎' SUBJECT1,ZZ_F4 result from zzpfapache2
??? union all
? select 27 line,ZZ_BINGRENID,ZZ_DATETIME , '有無功能不全免疫損害:免疫抑制狀態' SUBJECT1,ZZ_F5 result from zzpfapache2
?) t
order by ZZ_DATETIME ,line;

--打印時為多列，所以又加入一個多列視圖
CREATE OR REPLACE VIEW ZZPFDY2 AS
SELECT
a.subject1,
a.ZZ_DATETIME,a.n, A.ZZ_BINGRENID, A.result,
b.n bn, b.ZZ_BINGRENID bp,b.ZZ_DATETIME bt, B.result as result1,
c.n cn, c.ZZ_BINGRENID cp,c.ZZ_DATETIME ct, c.result as result2,
d.n dn, d.ZZ_BINGRENID dp,d.ZZ_DATETIME dt, d.result as result3,
e.n en, e.ZZ_BINGRENID ep,e.ZZ_DATETIME et, e.result as result4,
f.n fn, f.ZZ_BINGRENID fp,f.ZZ_DATETIME ft, f.result as result5
FROM

(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME,subject1
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME,subject1 FROM zzpf
?)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 0
) A,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result,ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
?)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 1
) B,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 2
) C,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 3
) D,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 4
) E,
(SELECT N, result, ZZ_BINGRENID,ZZ_DATETIME
FROM (SELECT ROWNUM N, result, ZZ_BINGRENID,ZZ_DATETIME FROM zzpf
)
WHERE MOD(FLOOR((N - 1) / 27), 6) = 5
) F

WHERE A.N + 27? = B.N(+)? and b.N + 27? = c.N(+) and
????? c.N + 27? = D.N(+)? and D.N + 27? = E.N(+) and
????? e.n + 27? = f.N(+);

--豎排視圖與打印視圖的中間視圖
create or replace view ZZPF as (select * from zzpfdy where ZZ_BINGRENID='1545187')