pivot(行转列)与unpivot(列转行)

--清楚缓存：
--每次查询前清空缓存，试试：
--10g以上：
alter system flush buffer_cache;
--9i：
ALTER SESSION SET EVENTS 'immediate trace name flush_cache'; 


--建表
create table test_zhi(
       test_zhi_id  number,
       test_zhi_name varchar2(10) default '' not null,
       test_zhi_address varchar2(50) default '' not null
)


--建序列
create sequence seq_id
       increment by 1    --每次加1
       start with 1      --从1开始
       nomaxvalue        --不设最大值
       nocycle           --一直累加，不循环
       nocache           --不建缓冲区
       --minvalue 1      --最小值
      
  /* 注：一般在项目中会用缓冲区，提高效率，（格式：cache 20）
   缺点，如果ORACLE死机或者其它意外情况，会出到序列不连续的情况
   如果设置了最大值，一定要设置累加循环（maxvalue 10000,cycle）*/


--自动增长触发器
drop trigger tri_seq_id


create trigger tri_seq_id  before
     insert on test_zhi for each row when(new.test_zhi_id is null)
  begin
     select seq_id.nexval into :new.test_zhi_id from dual;
  end;       








--插入数据
insert into test_zhi values(seq_id.nextval,'姓名2abcd','地址2abcd');






select * from test_zhi;


create or replace procedure proc_test_zhi as
   cursor cur_test_zhi is
      select * from test_zhi;
      
   v_name     test_zhi.test_zhi_name%type;
   v_address  test_zhi.test_zhi_address%type;
      




Select * from 
(
  Select zhi.*, rownum rn 
  From (select * from test_zhi) zhi 
  Where rownum <= 40
)
Where rn >= 1








--(start)把行转换成列：--------------------------------------------------


DEPTNO           REASON        NUM
------------ ---------- ----------
LEAN A               10          8
LEAN B               10          4
LEAN B               33          1
LEAN C               41          2
LEAN C               10          3
LEAN C               11          1
LEAN D               10          2
LEAN D               11          1


--转换成：
DEPTNO        REASON 10  REASON 11  REASON 33  REASON 41


------------ ---------- ---------- ---------- ----------


LEAN A                8          0          0          0


LEAN B                4          0          1          0


LEAN C                3          1          0          2


LEAN D                2          1          0          0




select deptno,
  max(decode(reason, 10, num, 0)) "reason 10",
       max(decode(reason, 11, num, 0)) "reason 11",
       max(decode(reason, 33, num, 0)) "reason 33",
       max(decode(reason, 41, num, 0)) "reason 41"
  from test
 group by deptno;
 
select name,
       sum(decode(zfname,'size1',1,0)) size1, 
       sum(decode(zfname,'size2',1,0)) size2,
       sum(decode(zfname,'size3',1,0)) size3    
  from test
 group by name 


--或：


select * from (
   select times_purchased, state_code
   from customers t
)
pivot -- 行转列
(
   count(state_code)
   for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased


--(end)把行转换成列：--------------------------------------------------



--pivot语法：
select * from table pivot(
要统计的列
 for 自定义列名 in('列名中的值1'as'要展示的字段1','列名中的值2'as'要展示的字段2')
)
--unpivot语法：
select * from table unpivot(
 自定义列名
   for 自定义列名 in(要转换的已有列名1as'要展示的字段1',要转换的已有列名2as'要展示的字段2')
  )


--(start)把列转换成行：--------------------------------------------------


select *
  from cust_matrix
unpivot   -- 列转行
(
  state_counts
    for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
)
order by "Puchase Frequency", state_code


--(end)把列转换成行：--------------------------------------------------






--(start)把行转换成列(字符串类型的)：--------------------------------------------------
create table test 
(  user_id number not null enable, 
   question_id number not null enable, 
   response varchar2(20) 
);


insert into test values (123,1,'apple'); 
insert into test values (123,1,'banana'); 
insert into test values (123,1,'mango'); 
insert into test values (123,2,'dog'); 
insert into test values (124,1,'grape'); 
insert into test values (124,2,'cat'); 
insert into test values (124,2,'dolphin');
insert into test values (125,1,'orange');
insert into test values (125,3,'usa');


select * from test;


select user_id,
       max(decode(question_id, 1, zhi)) Q1,
       max(decode(question_id, 2, zhi)) Q2,
       max(decode(question_id, 3, zhi)) Q3,
       max(decode(question_id, 4, zhi)) Q4
  from (select user_id, question_id, substr(max(sys_connect_by_path(response, ', ')), 2) zhi
          from (select t.*, row_number() over(partition by t.user_id, t.question_id order by t.response) rn
                  from test t)
         start with rn = 1
       connect by prior user_id = user_id
               and prior question_id = question_id
               and prior rn = rn - 1
         group by user_id, question_id)
 group by user_id
 order by user_id;
--(end)把行转换成列(字符串类型的)：--------------------------------------------------


/*


row_number()  顺序号码,  也就是 行号, 比如 1,2,3,4,5 这样的顺序。


over()  语法需要，必须的。


partition by t.user_id, t.question_id  是按照 t.user_id, t.question_id  分区。
也就是 如果有 不同的 t.user_id, t.question_id  , 这个 序号又重新从1开始计算。


order by t.response 是 排序方式， 也就是按最小的 t.response排序  
row_number()  是1，然后随着 t.response 的增加，  row_number() 不断递增。


*/