固定行列转换

SQL> select * from test;

CARD Q1 Q2
---------- ---------- ----------
a 1 12
a 1 21
a 2 43
a 2 65
b 1 12
b 2 87
b 2 1

7 rows selected.

SQL> SELECT
2 a.card,a.q1,
3 SUM(decode(a.q1, 1, a.q2, 0)) x1,
4 SUM(decode(a.q1, 2, a.q2, 0)) x2
5 FROM test a
6 GROUP BY a.card,a.q1
7 ORDER BY 1;

CARD Q1 X1 X2
---------- ---------- ---------- ----------
a 1 33 0
a 2 0 108
b 1 12 0
b 2 0 88

SQL> select * from test1;

CARD Q1 Q2 Q3 Q4
---------- ---------- ---------- ---------- ----------
a 3 1 2 3
b 1 2 3 4

SQL> SELECT
2 t.card,
3 decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) x1
4 FROM
5 (SELECT a.*, b.rn
6 FROM test1 a,
7 (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t
8 order by 1;

CARD X1
---------- ----------
a 3
a 1
a 2
a 3
b 3
b 4
b 1
b 2

8 rows selected.

SQL> select * from test2;

CARD Q1
---------- --------------------
1 a;b;c;d;e
2 b;g;h

SQL> SELECT t.card,
2 substr(t.q1,instr(';' || t.q1, ';', 1, rn),instr(t.q1 || ';', ';', 1, rn) - instr(';' || t.q1, ';', 1, rn)) q
3 FROM
4 (SELECT a.card, a.q1, b.rn FROM test2 a,
5 (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 5) b WHERE instr(';' || a.q1, ';', 1, rn) > 0) t ORDER BY 1, 2;

CARD Q
---------- --------------------
1 a
1 b
1 c
1 d
1 e
2 b
2 g
2 h

8 rows selected.

SQL> select * from test;

CARD Q1
-------------------- ----------
a 21
a 43
a 56
a 12
b 1
b 2
b 3
b 4

8 rows selected.

SQL> select card,max(sys_connect_by_path(q1,';')) result from
2 (
3 select card,q1,(row_number() over(order by card,q1 desc) + rank() over(order by card)) rn
4 from test
5 )
6 connect by rn-1 = prior rn
7 group by card;

CARD RESULT
-------------------- --------------------------------------------------
a ;56;43;21;12
b ;4;3;2;1

[@more@]

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

转载于:http://blog.itpub.net/10897379/viewspace-967714/