本文对ORACLE11G的行列转换操作的简单实例,供初学者参考。
现有问题如下:
问题描述:
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'
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24867611/viewspace-735349/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24867611/viewspace-735349/