--行列转换 行转列

　　DROP TABLE t_change_lc;

　　CREATE TABLE t_change_lc (card_code VARCHAR2(3), q NUMBER, bal NUMBER);

　　INSERT INTO t_change_lc

　　SELECT '001' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4

　　UNION

　　SELECT '002' card_code, ROWNUM q, trunc(dbms_random.VALUE * 100) bal FROM dual CONNECT BY ROWNUM <= 4;

　　SELECT * FROM t_change_lc;

　　SELECT a.card_code,

　　SUM(decode(a.q, 1, a.bal, 0)) q1,

　　SUM(decode(a.q, 2, a.bal, 0)) q2,

　　SUM(decode(a.q, 3, a.bal, 0)) q3,

　　SUM(decode(a.q, 4, a.bal, 0)) q4

　　FROM t_change_lc a

　　GROUP BY a.card_code

　　ORDER BY 1;

　　--行列转换 列转行

　　DROP TABLE t_change_cl;

　　CREATE TABLE t_change_cl AS

　　SELECT a.card_code,

　　SUM(decode(a.q, 1, a.bal, 0)) q1,

　　SUM(decode(a.q, 2, a.bal, 0)) q2,

　　SUM(decode(a.q, 3, a.bal, 0)) q3,

　　SUM(decode(a.q, 4, a.bal, 0)) q4

　　FROM t_change_lc a

　　GROUP BY a.card_code

　　ORDER BY 1;

　　SELECT * FROM t_change_cl;

　　SELECT t.card_code,

　　t.rn q,

　　decode(t.rn, 1, t.q1, 2, t.q2, 3, t.q3, 4, t.q4) bal

　　FROM (SELECT a.*, b.rn

　　FROM t_change_cl a,

　　(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 4) b) t

　　ORDER BY 1, 2;

　　--行列转换 行转列 合并

　　DROP TABLE t_change_lc_comma;

　　CREATE TABLE t_change_lc_comma AS SELECT card_code,'quarter_'||q AS q FROM t_change_lc;

　　SELECT * FROM t_change_lc_comma;

　　SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q

　　FROM (SELECT a.card_code,

　　a.q,

　　row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn

　　FROM t_change_lc_comma a) t1

　　START WITH t1.rn = 1

　　CONNECT BY t1.card_code = PRIOR t1.card_code

　　AND t1.rn - 1 = PRIOR t1.rn

　　GROUP BY t1.card_code;

　　--行列转换 列转行 分割

　　DROP TABLE t_change_cl_comma;

　　CREATE TABLE t_change_cl_comma AS

　　SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q

　　FROM (SELECT a.card_code,

　　a.q,

　　row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn

　　FROM t_change_lc_comma a) t1

　　START WITH t1.rn = 1

　　CONNECT BY t1.card_code = PRIOR t1.card_code

　　AND t1.rn - 1 = PRIOR t1.rn

　　GROUP BY t1.card_code;

　　SELECT * FROM t_change_cl_comma;

　　SELECT t.card_code,

　　substr(t.q,

　　instr(';' || t.q, ';', 1, rn),

　　instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q

　　FROM (SELECT a.card_code, a.q, b.rn

　　FROM t_change_cl_comma a,

　　(SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b

　　WHERE instr(';' || a.q, ';', 1, rn) > 0) t

　　ORDER BY 1, 2;