pivot的用法

create table SALES
(
  sales_id          NUMBER not null,
  inner_code        VARCHAR2(255) not null,
  org_id            NUMBER not null,
  customer_id       NUMBER,
  sales_date        DATE not null,
  type_id           NUMBER,
  create_user_id    NUMBER not null,
  address           VARCHAR2(4000),
  postcode          VARCHAR2(255),
  recipient_phone   VARCHAR2(255),
  recipient_name    VARCHAR2(255),
  description       VARCHAR2(4000 CHAR),
  create_time       DATE,
  create_user_name  VARCHAR2(4000 CHAR),
  org_name          VARCHAR2(4000 CHAR),
  customer_name     VARCHAR2(4000 CHAR),
  status            NUMBER,
  instance_id       NUMBER,
  enterprise_id     NUMBER not null,
  customer_code     VARCHAR2(4000),
  org_code          VARCHAR2(4000),
  create_user_code  VARCHAR2(4000),
  pay_num           VARCHAR2(255),
  in_user_id        NUMBER,
  in_user_name      VARCHAR2(255),
  in_user_code      VARCHAR2(255),
  out_user_id       NUMBER,
  out_user_name     VARCHAR2(255),
  out_user_code     VARCHAR2(255),
  invoice_date      DATE,
  invoice_code      VARCHAR2(255),
  sales_amount      NUMBER,
  sales_amount_cost NUMBER,
  sales_amount_tax  NUMBER,
  sales_tax         NUMBER,
  term_id           NUMBER,
  end_date          DATE,
  sales_org_id      NUMBER,
  sales_org_name    VARCHAR2(255),
  sales_org_code    VARCHAR2(255),
  sales_team_id     NUMBER,
  sales_team_name   VARCHAR2(255),
  sales_user_id     NUMBER,
  sales_user_name   VARCHAR2(255),
  sales_user_code   VARCHAR2(255)
);

create table SALES_LINE
(
  line_id           NUMBER not null,
  sales_id          NUMBER not null,
  product_id        NUMBER,
  warehouse_id      NUMBER,
  quantity          NUMBER not null,
  amount            NUMBER not null,
  amount_cost       NUMBER,
  position_id       NUMBER,
  warehouse_name    VARCHAR2(255),
  brand_name        VARCHAR2(255),
  brand_id          NUMBER,
  product_name      VARCHAR2(255),
  position_name     VARCHAR2(255),
  unit_name         VARCHAR2(255),
  status            NUMBER,
  product_type_id   NUMBER,
  product_type_name VARCHAR2(255),
  o_id              NUMBER,
  product_code      VARCHAR2(255),
  warehouse_code    VARCHAR2(255),
  brand_code        VARCHAR2(255),
  product_spec      VARCHAR2(255 CHAR),
  tax_amount        NUMBER,
  tax_rate          NUMBER(22,2),
  total_tax         NUMBER(22,2),
  tax_id            NUMBER,
  return_line_id    NUMBER,
  sn_number         NUMBER,
  paul_price        NUMBER,
  sn_codes          VARCHAR2(4000)
);

with t as

 (select s.sales_date, sl.brand_name, sl.brand_id,sl.amount
    from sales_line sl
    join sales s on s.sales_id=sl.sales_id
   where sl.brand_name in ('索尼数码相机', '联想笔记本配件', '宏基笔记本'))
select * from t
pivot (sum(amount) amount,avg(amount) amountavg for brand_name in('索尼数码相机', '联想笔记本配件', '宏基笔记本'));




with t as
 (select s.sales_date,sl.brand_id,sl.amount
    from sales_line sl
    join sales s on s.sales_id=sl.sales_id
   )
select * from t
pivot (sum(amount) amount,avg(amount) amountavg for (brand_id) in(34,2424,2394));




select * from (
select s.customer_id,s.customer_name,s.customer_code,sl.brand_name,brand_id,amount from sales_line sl join sales s on s.sales_id=sl.sales_id
) pivot(sum(amount) amount for brand_id in (34,2424,2394));