Oralce 行列转换

数据格式一
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

 

从格式一到格式二

[java] view plain copy print ?

1. SELECT t1.card_code, substr(MAX(sys_connect_by_path(t1.q, ';')), 2) q  
2.   FROM (SELECT a.card_code,  
3.                a.q,  
4.                row_number() over(PARTITION BY a.card_code ORDER BY a.q) rn  
5.           FROM t_change_lc_comma a) t1  
6.  START WITH t1.rn = 1  
7. CONNECT BY t1.card_code = PRIOR t1.card_code  
8.        AND t1.rn - 1 = PRIOR t1.rn  
9.  GROUP BY t1.card_code;  

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;

 

技巧：
–根据分组后的行号，构造一棵树（或者多棵）。
–把从根到叶子的值串接起来。

 

 从格式二到格式一

 

[java] view plain copy print ?

1. SELECT t.card_code,  
2.       substr(t.q,  
3.              instr(';' || t.q, ';', 1, rn),  
4.              instr(t.q || ';', ';', 1, rn) - instr(';' || t.q, ';', 1, rn)) q  
5.  FROM (SELECT a.card_code, a.q, b.rn  
6.          FROM t_change_cl_comma a,  
7.               (SELECT ROWNUM rn FROM dual CONNECT BY ROWNUM <= 100) b  
8.         WHERE instr(';' || a.q, ';', 1, rn) > 0) t  
9. ORDER BY 1, 2;