以下是我曾经写的一个例子:
with tmp_tab as(
select p.patient_id,
i.patient_name,
TRUNC(i.plan_time) as ,
i.vitalsign_nval1 || '/' || i.vitalsign_nval2 as xy,
row_number() OVER(PARTITION BY p.patient_id, TRUNC(i.plan_time) ORDER BY TRUNC(i.plan_time) desc) as row_flg from MCS_VITAL_INFO i, mcs_his_patient p
where p.patient_uid = i.patient_uid and i.is_valid = 1 and i.vitalsign_type='1005' )
select * from tmp_tab pivot(max(xy) for row_flg in (
'1' as first_bp,
'2' as second_bp
)
)
这个是将两个表合并,两个表的需要合在一起,但是还要将多行出现的数据,转换为一个数据的单列。
1.血压值合并
2.日期去掉时分秒
3.统计每天病人的数据排序
4.行转列的临时表再采用select函数,结合1血压值合并的xy