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  第一單 橘子,蘋果,香蕉

通常是配合替換函數使用:

因為作者水準有限,難免有疏漏之處.望讀者不吝批評指正!