Oracle数据库行显示转换成列显示--pivot的应用

来源：互联网发布：linux下重启mysql 编辑：程序博客网时间：2024/05/16 14:23

Create table tmp(types varchar(22) primary key,num int,maps int);insert into tmp (types, num, maps)values ('计划收储', 635, 50252909); insert into tmp (types, num, maps)values ('实际供应', 6, 588846.3666); insert into tmp (types, num, maps)values ('已出库', 2, 137); insert into tmp (types, num, maps)values ('已入库', 1, 75200); insert into tmp (types, num, maps)values ('已收储', 2, 84075200);

查询数据如下：select * from tmp;

types num maps

计划收储 635 50252909
实际供应 6 588846
已出库 2 137
已入库 1 75200
已收储 2 84075200

要求显示成如下形式：

计划收储计划收储已出库已入库已收储地块数6356212面积502529095888461377520084075200

执行sql：

(select '地块数' as " ",sum(decode(types，'计划收储',num,0)) as 计划收储,                      sum(decode(types，'实际供应',num,0)) as 实际供应,                      sum(decode(types，'已出库',num,0)) as 已出库,                                                                 sum(decode(types，'已入库',num,0)) as 已入库,                      sum(decode(types，'已收储',num,0)) as 已收储 from tmp)union all(select '面积' as " ",sum(decode(types，'计划收储',maps,0)) as 计划收储,                      sum(decode(types，'实际供应',maps,0)) as 实际供应,                      sum(decode(types，'已出库',maps,0)) as 已出库,                                                                 sum(decode(types，'已入库',maps,0)) as 已入库,                      sum(decode(types，'已收储',maps,0)) as 已收储 from tmp)

下面换成使用关键字pivot实现

select * from (select '地块数' " ", types,num from tmp)  pivot (sum(num) for types in ('计划收储' 计划收储, '实际供应'  实际供应 , '已出库' 已出库 , '已入库' 已入库,'已收储' 已收储))union allselect * from (select  '面积' " ", types,maps from tmp)  pivot (sum(maps)  for types in ('计划收储' 计划收储, '实际供应'  实际供应 , '已出库' 已出库 , '已入库' 已入库,'已收储' 已收储))

阅读全文

0 0