ORACLE 行专列 PIVOT 函数的应用

来源：互联网发布：咫尺网络下载编辑：程序博客网时间：2024/04/29 21:41

首先,做准备工作

建表:

-- Create tablecreate table DEMO(  n_iden       NUMBER,  c_order_code NVARCHAR2(50),  c_order_name NVARCHAR2(50),  c_friut      NVARCHAR2(50),  c_amount     NUMBER(20,4),  d_build      DATE,  c_buyer      NVARCHAR2(50),  c_seller     NVARCHAR2(50))tablespace MYORCL1  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );-- Add comments to the columns comment on column DEMO.n_iden  is 'ID';comment on column DEMO.c_order_code  is '订单号';comment on column DEMO.c_order_name  is '订单名称';comment on column DEMO.c_friut  is '水果类型';comment on column DEMO.c_amount  is '水果数量';comment on column DEMO.d_build  is '创建日期';comment on column DEMO.c_buyer  is '订货人';comment on column DEMO.c_seller  is '买货人';

插入数据:

insert into DEMO (N_IDEN, C_ORDER_CODE, C_ORDER_NAME, C_FRIUT, C_AMOUNT, D_BUILD, C_BUYER, C_SELLER)values (1, 'order_num1', '第一单', '苹果', 1.0000, to_date('11-01-2017', 'dd-mm-yyyy'), null, null);insert into DEMO (N_IDEN, C_ORDER_CODE, C_ORDER_NAME, C_FRIUT, C_AMOUNT, D_BUILD, C_BUYER, C_SELLER)values (2, 'order_num1', '第一单', '橘子', 2.0000, to_date('11-01-2017', 'dd-mm-yyyy'), null, null);insert into DEMO (N_IDEN, C_ORDER_CODE, C_ORDER_NAME, C_FRIUT, C_AMOUNT, D_BUILD, C_BUYER, C_SELLER)values (3, 'order_num1', '第一单', '香蕉', 3.0000, to_date('11-01-2017', 'dd-mm-yyyy'), null, null);insert into DEMO (N_IDEN, C_ORDER_CODE, C_ORDER_NAME, C_FRIUT, C_AMOUNT, D_BUILD, C_BUYER, C_SELLER)values (4, 'order_num2', '第二单', '苹果', 4.0000, to_date('10-01-2017', 'dd-mm-yyyy'), null, null);insert into DEMO (N_IDEN, C_ORDER_CODE, C_ORDER_NAME, C_FRIUT, C_AMOUNT, D_BUILD, C_BUYER, C_SELLER)values (5, 'order_num2', '第二单', '橘子', 5.0000, to_date('10-01-2017', 'dd-mm-yyyy'), null, null);

如果我们要实现以订单名称和订单代码为一个单位的数据时

order_code order_name 苹果橘子香蕉

order_num1 第一单 1 2 3
order_num2 第二单 4 5 0

普通的实现方式比较复杂,并且当苹果种类较多的时候会变得极其不适用

普通方法的代码如下

select c_order_code ,c_order_name,sum(decode(c_friut,'苹果',c_amount,0)) 苹果, sum(decode(c_friut,'橘子',c_amount,0)) 橘子, sum(decode(c_friut,'香蕉',c_amount,0)) 香蕉 from DEMO group by c_order_code ,c_order_name

用 PIVOT 函数如下

select * from (
(select c_order_code,c_order_name,c_friut,C_AMOUNT from demo) pivot( sum(C_AMOUNT)for c_friut in ('苹果','橘子','香蕉') )
)

翻译:pivot :轴,中心

sum() :单聚合函数

for:对于,关于

c_friut : 转换为行的字段

in('苹果','橘子','香蕉') : 以三种水果为分组

上述两种的查询结果为:

还有一种行专列的形式,是用 wm_concat函数

select c_order_code,c_order_name, to_char(wm_concat(c_friut)) from demo group by c_order_code,c_order_name;

order_num1 第一单橘子,苹果,香蕉

通常是配合替换函数使用:

因为作者水平有限,难免有疏漏之处.望读者不吝批评指正!

0 0