文对Oracle 11g的行列转换操作的简单实例,供初学者参考。
现有问题如下:
问题描述:
Table1
Id Name
1 Taylor
2 Jim
Table2
FId value attr-
1 23 age
1 boy sex
2 26 age
2 boy sex
Table1 Id 是主键,Table2 的FId 是外键,对应Table1 的主键
要查出age大于24,且sex 是boy 的人的name
结果是:
Name
Jim
创建表病插入数据,其中TABLE1为源表,TABLE2为目标表,脚本如下:
create table table2(
fid number(12),
value varchar2(10),
attr varchar2(10)
);
insert into table2 values(1,'23','age');
insert into table2 values(1,'boy','sex');
insert into table2 values(2,'26','age');
insert into table2 values(2,'boy','sex');
create table table1(
id number(12),
name varchar2(20)
);
insert into table1 values(1,'Taylor');
insert into table1 values(2,'Jim');
如下语句可实现表TABLE1的行列转换:
select *
from table2 pivot(max(value) as attr for(attr) in('age' as age,
'sex' as sex));
最后如下语句即可解答开始的问题:
with pivot_table2 as(
select *
from table2 pivot(max(value) as attr for(attr) in('age' as age,
'sex' as sex)))
select t1.* from pivot_table2 pt,table1 t1
where pt.fid=t1.id
and pt.age_attr>24 and pt.sex_attr='boy'
本文出自:亿恩科技【www.enkj.com】