行列转列(很实用)

行列转列

数据格式一
CARD_CODE          Q        BAL
--------- ---------- ----------
001                1         27
001                2         10
001                3         36
001                4         97
002                1         96
002                2         12
002                3         15
002                4         32

数据格式二
CARD_CODE         Q1         Q2         Q3         Q4
--------- ---------- ---------- ---------- ----------
001               27         10         36         97
002               96         12         15         32

从格式一到格式二

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 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;

技巧：
构造虚拟表把源数据的行数增加。
根据行号选择显示不同的字段

数据格式一
CARD_CODE Q
--------- ------------------------------------------------
001       quarter_1
001       quarter_2
001       quarter_3
001       quarter_4
002       quarter_1
002       quarter_2
002       quarter_3
002       quarter_4

数据格式二
CARD_CODE Q
--------- --------------------------------------------------------------------------------
002       quarter_1;quarter_2;quarter_3;quarter_4
001       quarter_1;quarter_2;quarter_3;quarter_4

从格式一到格式二

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 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;

技巧：
构造虚拟表把源数据的行数增加。
根据行号选择显示字段的不同部分