ORACLE行转列、列转行函数

创建所需表inp_bill_detail

create table inp_bill_detail(
patient_id varchar2(16) not null,
visit_id number(2) not null,
item_no number(6) not null,
item_code varchar2(20),
item_name varchar2(100)
);
comment on column inp_bill_detail.patient_id is '患者编号';
comment on column inp_bill_detail.visit_id is '就诊次数';
comment on column inp_bill_detail.item_no is '项目序号';
comment on column inp_bill_detail.item_code is '项目编号';
comment on column inp_bill_detail.item_name is '项目名称';
alter table inp_bill_detail add constraint pk_inp_bill_dettail primary key(patient_id,visit_id,item_no);

插入数据

insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,1,'HL001','一级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,2,'HL001','一级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,3,'HL001','一级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,4,'HL002','二级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,5,'HL002','二级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,6,'HL002','二级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,7,'HL002','二级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,8,'HL003','三级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,9,'HL003','三级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,10,'HL003','三级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,11,'HL003','三级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,12,'HL003','三级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,13,'HL004','特级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,14,'HL004','特级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,15,'HL004','特级护理');
insert into inp_bill_detail(patient_id,visit_id,item_no,item_code,item_name) values('19960509',1,16,'HL004','特级护理');

查询患者各级护理的次数

select ibd.patient_id,ibd.visit_id,ibd.item_name,count(*) as sl from inp_bill_detail ibd group by ibd.patient_id,ibd.visit_id,ibd.item_name order by decode(ibd.item_name,'一级护理',1,'二级护理',2,'三级护理',3,4)

查询结果：

patient_id	visit_id	item_name	sl
19960509	1	一级护理	3
19960509	1	二级护理	4
19960509	1	三级护理	5
19960509	1	特级护理	4

行转列

with aa as(
select ibd.patient_id,ibd.visit_id,ibd.item_name,count(*) as sl from inp_bill_detail ibd group by ibd.patient_id,ibd.visit_id,ibd.item_name order by decode(ibd.item_name,'一级护理',1,'二级护理',2,'三级护理',3,4)
)
select * from aa pivot(max(aa.sl) for aa.item_name in('一级护理' 一级护理,'二级护理' 二级护理,'三级护理' 三级护理,'特级护理' 特级护理))

查询结果

patient_id	visit_id	一级护理	二级护理	三级护理	特级护理
19960509	1	3	4	5	4

列转行函数–unpivot

创建表

create table lzh_temp as
with aa as(
select ibd.patient_id,ibd.visit_id,ibd.item_name,count(*) as sl from inp_bill_detail ibd group by ibd.patient_id,ibd.visit_id,ibd.item_name order by decode(ibd.item_name,'一级护理',1,'二级护理',2,'三级护理',3,4)
)
select * from aa pivot(max(aa.sl) for aa.item_name in('一级护理' 一级护理,'二级护理' 二级护理,'三级护理' 三级护理,'特级护理' 特级护理))

列转行

select * from lzh_temp unpivot(SL for NAME in(一级护理,二级护理,三级护理,特级护理))

查询结果

patient_id	visit_id	item_name	sl
19960509	1	一级护理	3
19960509	1	二级护理	4
19960509	1	三级护理	5
19960509	1	特级护理	4

注意
列转行in()中填写的为列名，不得添加引号；SL,NAME为自定义列名