使用dimension by实现行列转换

--建一张测试表
create table user_rank_book_inc_cdr
(
  record_day varchar2(8),
  msisdn varchar2(20),
  bookid1 varchar2(19),
  bookid2 varchar2(19),
  bookid3 varchar2(19),
  bookid4 varchar2(19),
  bookid5 varchar2(19),
  bookid6 varchar2(19),
  bookid7 varchar2(19),
  bookid8 varchar2(19),
  bookid9 varchar2(19),
  bookid10 varchar2(19),
  bookid11 varchar2(19),
  bookid12 varchar2(19)
)

--插入数据

begin
  for i in 20140801 .. 20140830 loop
    insert into user_rank_book_inc_cdr
      (record_day,
       msisdn,
       bookid1,
       bookid2,
       bookid3,
       bookid4,
       bookid5,
       bookid6,
       bookid7,
       bookid8,
       bookid9,
       bookid10,
       bookid11,
       bookid12)
    values
      (to_char(i),
       '111'||to_char(i),
       i||'01',
       i||'02',
       i||'03',
       i||'04',
       i||'05',
       i||'06',
       i||'07',
       i||'08',
       i||'09',
       i||'10',
       i||'11',
       i||'12');
    commit;
  end loop;
end;
/

--验证数据
select count(*)  from user_rank_book_inc_cdr;
30

--测试脚本
select bookid
  from user_rank_book_inc_cdr
 where msisdn = '11120140801' model return updated rows partition
 by(msisdn) dimension by(0 as n) measures('wwwwwwwwwwww' as bookid,
                bookid1,
                bookid2,
                bookid3,
                bookid4,
                bookid5,
                bookid6,
                bookid7,
                bookid8,
                bookid9,
                bookid10,
                bookid11,
                bookid12) rules upsert
 all(bookid [ 1 ] = bookid1 [ 0 ],
           bookid [ 2 ] = bookid2 [ 0 ],
           bookid [ 3 ] = bookid3 [ 0 ],
           bookid [ 4 ] = bookid4 [ 0 ],
           bookid [ 5 ] = bookid5 [ 0 ],
           bookid [ 6 ] = bookid6 [ 0 ],
           bookid [ 7 ] = bookid7 [ 0 ],
           bookid [ 8 ] = bookid8 [ 0 ],
           bookid [ 9 ] = bookid9 [ 0 ],
           bookid [ 10 ] = bookid10 [ 0 ],
           bookid [ 11 ] = bookid11 [ 0 ],
           bookid [ 12 ] = bookid12 [ 0 ]);


       BOOKID
1        2014080112
2        2014080111
3        2014080110
4        2014080109
5        2014080108
6        2014080107
7        2014080106
8        2014080105
9        2014080104
10    2014080103
11    2014080102
12    2014080101

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/692830/viewspace-1260316/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/692830/viewspace-1260316/