oracle 一行列转换问题

1. //A表：   
2. c1 c2  
3. 22 a  
4. 25 b  
5. 26 c  
6. 46 d  
7. //B表：   
8. c3 c4  
9. 1 “22,25,26,46,”  
10. //结果：   
11. tb_c3 ta_c2  
12. 1     a，b,c,d  
13. //分析：   
14. //从结果可以看出来，这是一个将行数据转换为列数据的问题，可以根据b表中的c4列来连接a,b两个表；   
15. //现在的首要问题是，将b表中的c4列转换为4个行，然后与a表进行等值连接；   
16. //最后将上一步操作得到的数据，使用wm_concat()字符串连接函数，在按照一定的类分组就可以得到结果   
17. //解法一：   
18. //1.首先将b表中的c4列转换为行：   
19. //这里需要将c4列中的双引号去掉(用空串来替换)   
20. with tb as(  
21.      select 1 c3,'"22,25,26,46"' c4 from dual)  
22. select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5  
23. from tb  
24. connect by  
25.         level <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1;  
26. /  
27.         C3 C5  
28. ---------- ----------------------  
29.          1 22  
30.          1 25  
31.          1 26  
32.          1 46  
33. //2.然后将第1不得到的结果与ta表进行等值连接：   
34. with ta as(  
35.      select 22 c1, 'a' c2 from dual union all  
36.      select 25,'b' from dual union all  
37.      select 26,'c' from dual union all  
38.      select 46,'d' from dual)  
39. , tb as(  
40.      select 1 c3,'"22,25,26,46"' c4 from dual)  
41. select td.c3,ta.c2  
42. from ta,(  
43.      select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5  
44.      from tb  
45.      connect by  
46.              level <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1) td  
47. where ta.c1 = td.c5  
48. /  
49.         C3 C2  
50. ---------- --  
51.          1 a  
52.          1 b  
53.          1 c  
54.          1 d  
55. //3.使用wm_concat字符串连接函数，将第2步得到的结果连接起来：   
56. //这样就能得到我们的结果了：   
57. with ta as(  
58.      select 22 c1, 'a' c2 from dual union all  
59.      select 25,'b' from dual union all  
60.      select 26,'c' from dual union all  
61.      select 46,'d' from dual)  
62. , tb as(  
63.      select 1 c3,'"22,25,26,46"' c4 from dual)  
64. select te.a tb_c3,wm_concat(te.b) ta_c2  
65. from (  
66.       select td.c3 a,ta.c2 b  
67.       from ta,(  
68.            select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5  
69.            from tb  
70.            connect by  
71.            level <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1) td  
72.       where ta.c1 = td.c5) te  
73. group by a  
74. /  
75.    
76.      TB_C3 TA_C2  
77. ---------- --------------  
78.          1 a,b,c,d  
79. //解法二：   
80. with ta as(  
81.      select 22 c1, 'a' c2 from dual union all  
82.      select 25,'b' from dual union all  
83.      select 26,'c' from dual union all  
84.      select 46,'d' from dual)  
85. , tb as(  
86.      select 1 c3,'"22,25,26,46"' c4 from dual)  
87. select c3,max(substr(sys_connect_by_path(c2,','),2)) a  
88. from (  
89.       select c3,c2,rownum rn,rownum - 1 rm  
90.       from (  
91.             select a.c1,a.c2,b.c3,b.c4  
92.             from ta a,tb b)  
93.       where instr(c4,c1) > 0)  
94. start with rn=1  
95. connect by prior rn=rm  
96. group by c3  
97. /  
98.    
99.         C3 A  
100. ---------- ------------------  
101.          1 a,b,c,d