oracle 多列unpivot及unpivot 使用

这是一个复杂的例子，想对它有一个深入的使用，那仔细看看吧

点击(此处)折叠或打开

1. create table TT_TEST2
   
2. (
   
3.   allup_level NUMBER(2),
   
4.   cross_flag NUMBER(1),
   
5.   book_id NUMBER(10),
   
6.   bit_state VARCHAR2(10),
   
7.   pool1_id NUMBER(10),
   
8.   selection1_no VARCHAR2(10),
   
9.   goal_line1 NUMBER(5,2),
   
10.   pool2_id NUMBER(10),
    
11.   selection2_no VARCHAR2(10),
    
12.   goal_line2 NUMBER(5,2),
    
13.   pool3_id NUMBER(10),
    
14.   selection3_no VARCHAR2(10),
    
15.   goal_line3 NUMBER(5,2),
    
16.   pool4_id NUMBER(10),
    
17.   selection4_no VARCHAR2(10),
    
18.   goal_line4 NUMBER(5,2),
    
19.   pool5_id NUMBER(10),
    
20.   selection5_no VARCHAR2(10),
    
21.   goal_line5 NUMBER(5,2),
    
22.   pool6_id NUMBER(10),
    
23.   selection6_no VARCHAR2(10),
    
24.   goal_line6 NUMBER(5,2),
    
25.   pool7_id NUMBER(10),
    
26.   selection7_no VARCHAR2(10),
    
27.   goal_line7 NUMBER(5,2),
    
28.   pool8_id NUMBER(10),
    
29.   selection8_no VARCHAR2(10),
    
30.   goal_line8 NUMBER(5,2),
    
31.   ticket_count NUMBER(10),
    
32.   investment NUMBER(19),
    
33.   liability NUMBER(22,2),
    
34.   act_liability NUMBER(22,2)
    
35. );
    
36. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
    
37. values (3, 0, 0, \'0\', 2293079, \'+5\', null, 2293087, \'+3\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 1767.74, 0);
    
38. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
    
39. values (2, 0, 0, \'0\', 2293079, \'+5\', null, 2293087, \'+3\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 883.87, 0);
    
40. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
    
41. values (2, 0, 0, \'0\', 2293087, \'+3\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 792, 0);
    
42. insert into TT_TEST2 (allup_level, cross_flag, book_id, bit_state, pool1_id, selection1_no, goal_line1, pool2_id, selection2_no, goal_line2, pool3_id, selection3_no, goal_line3, pool4_id, selection4_no, goal_line4, pool5_id, selection5_no, goal_line5, pool6_id, selection6_no, goal_line6, pool7_id, selection7_no, goal_line7, pool8_id, selection8_no, goal_line8, ticket_count, investment, liability, act_liability)
    
43. values (2, 0, 0, \'0\', 2293079, \'+5\', null, 2293127, \'-2\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 0, \'0\', null, 1, 198, 883.87, 0);
    
44. commit;

以上是基础数据，有点长，
以下是sql

点击(此处)折叠或打开

1. WITH a AS
   
2.        (SELECT allup_level,
   
3.                cross_flag,
   
4.                pool_id,
   
5.                selection_no,
   
6.                ticket_count,
   
7.                investment
   
8.         FROM TT_TEST2 p unpivot
   
9. 
   
10. ((pool_id, selection_no) FOR(x1, x2) IN((pool1_id,
    
11.                                                                                 selection1_no),
    
12.                                                                                (pool2_id,
    
13.                                                                                 selection2_no),
    
14.                                                                                (pool3_id,
    
15.                                                                                 selection3_no),
    
16.                                                                                (pool4_id,
    
17.                                                                                 selection4_no),
    
18.                                                                                (pool5_id,
    
19.                                                                                 selection5_no),
    
20.                                                                                (pool6_id,
    
21.                                                                                 selection6_no),
    
22.                                                                                (pool7_id,
    
23.                                                                                 selection7_no),
    
24.                                                                                (pool8_id,
    
25.                                                                                 selection8_no)))
    
26.         WHERE pool_id > 0)
    
27.       SELECT *
    
28.       FROM a pivot ( SUM(decode(cross_flag, 1, ticket_count, 0)) AS tx, SUM(decode(cross_flag, 1, 0, ticket_count)) AS t, SUM(decode(cross_flag, 1, investment, 0)) AS ix, SUM(decode(cross_flag, 1, 0, investment)) AS i FOR allup_level IN(2 AS v2,
    
29.                                                                                                                                                                                                                                              3 AS v3,
    
30.                                                                                                                                                                                                                                              4 AS v4,
    
31.                                                                                                                                                                                                                                              5 AS v5,
    
32.                                                                                                                                                                                                                                              6 AS v6,
    
33.                                                                                                                                                                                                                                              7 AS v7,
    
34.                                                                                                                                                                                                                                              8 AS v8))

不解释，自己感受一下

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

转载于:http://blog.itpub.net/134308/viewspace-1175926/