[面试] Oracle 11g 行列互换 pivot 和 unpivot

pivot 列转行
测试数据 (id,类型名称,销售数量),案例:根据水果的类型查询出一条数据显示出每种类型的销售数量。

 

?
1
2
3
4
5
6
7
8
9
create table demo(id int , name varchar (20),nums int );  ---- 创建表
insert into demo values (1, '苹果' , 1000);
insert into demo values (2, '苹果' , 2000);
insert into demo values (3, '苹果' , 4000);
insert into demo values (4, '橘子' , 5000);
insert into demo values (5, '橘子' , 3000);
insert into demo values (6, '葡萄' , 3500);
insert into demo values (7, '芒果' , 4200);
insert into demo values (8, '芒果' , 5500);

 

分组查询 (当然这是不符合查询一条数据的要求的)

?
1
select name , sum (nums) nums from demo group by name

 


行转列查询

 

?
1
select * from ( select name , nums from demo) pivot ( sum (nums) for name in ( '苹果' 苹果, '橘子' , '葡萄' , '芒果' ));


 

注意: pivot(聚合函数 for 列名 in(类型)) ,其中 in(‘’) 中可以指定别名,in中还可以指定子查询,比如 select distinct code from customers

当然也可以不使用pivot函数,等同于下列语句,只是代码比较长,容易理解

 

?
1
2
select * from select sum (nums) 苹果 from demo where name = '苹果' ),( select sum (nums) 橘子 from demo where name = '橘子' ),
        select sum (nums) 葡萄 from demo where name = '葡萄' ),( select sum (nums) 芒果 from demo where name = '芒果' );
unpivot 行转列
顾名思义就是将多列转换成1列中去
案例: 现在有一个水果表,记录了4个季度的销售数量,现在要将每种水果的每个季度的销售情况用多行数据展示。

 

创建表和数据

 

?
1
2
3
4
5
6
7
create table Fruit(id int , name varchar (20), Q1 int , Q2 int , Q3 int , Q4 int );
 
insert into Fruit values (1, '苹果' ,1000,2000,3300,5000);
insert into Fruit values (2, '橘子' ,3000,3000,3200,1500);
insert into Fruit values (3, '香蕉' ,2500,3500,2200,2500);
insert into Fruit values (4, '葡萄' ,1500,2500,1200,3500);
select * from Fruit

 

列转行查询

 

?
1
select id , name , jidu, xiaoshou from Fruit unpivot (xiaoshou for jidu in (q1, q2, q3, q4) )
注意: unpivot没有聚合函数,xiaoshou、jidu字段也是临时的变量

 

同样不使用unpivot也可以实现同样的效果,只是sql语句会很长,而且执行速度效率也没有前者高

 

?
1
2
3
4
5
6
7
select id, name , 'Q1' jidu, ( select q1 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name , 'Q2' jidu, ( select q2 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name , 'Q3' jidu, ( select q3 from fruit where id=f.id) xiaoshou from Fruit f
union
select id, name , 'Q4' jidu, ( select q4 from fruit where id=f.id) xiaoshou from Fruit f

XML类型
上述pivot列转行示例中,你已经知道了需要查询的类型有哪些,用in()的方式包含,假设如果您不知道都有哪些值,您怎么构建查询呢?

pivot 操作中的另一个子句 XML 可用于解决此问题。该子句允许您以 XML 格式创建执行了 pivot 操作的输出,在此输出中,您可以指定一个特殊的子句 ANY 而非文字值

示例如下:

 

?
1
2
3
4
5
6
7
select * from (
    select name , nums as "Purchase Frequency"
    from demo t
)                             
pivot xml (
    sum (nums) for name in ( any )
)

如您所见,列 NAME_XML 是 XMLTYPE,其中根元素是 <PivotSet>。每个值以名称-值元素对的形式表示。您可以使用任何 XML 分析器中的输出生成更有用的输出。

 


 

结论

Pivot 为 SQL 语言增添了一个非常重要且实用的功能。您可以使用 pivot 函数针对任何关系表创建一个交叉表报表,而不必编写包含大量 decode 函数的令人费解的、不直观的代码。同样,您可以使用 unpivot 操作转换任何交叉表报表,以常规关系表的形式对其进行存储。Pivot 可以生成常规文本或 XML 格式的输出。如果是 XML 格式的输出,您不必指定 pivot 操作需要搜索的值域。

参考: http://www.oracle-developer.net/display.php?id=506


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值