ORACLE 行专列 PIVOT 函数的应用

首先,做准备工作

建表:

-- Create table
create 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  第一单 橘子,苹果,香蕉

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

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