oracle 一个有趣的行列转换问题

uwmbilypum

　　//一个有趣的行列转换//A表：c1 c222 a25 b26 c46 d//B表：c3 c41 “22,25,26,46,”//结果：tb_c3 ta_c21     a，b,c,d//分析：//从结果可以看出来，这是一个将行数据转换为列数据的问题，可以根据b表中的c4列来连接a,b两个表；//现在的首要问题是，将b表中的c4列转换为4个行，然后与a表进行等值连接；//最后将上一步操作得到的数据，使用wm_concat()字符串连接函数，在按照一定的类分组就可以得到结果//解法一：//1.首先将b表中的c4列转换为行：//这里需要将c4列中的双引号去掉(用空串来替换)with tb as(select 1 c3,'"22,25,26,46"' c4 from dual)select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5from tbconnect bylevel <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1;/C3 C5---------- ----------------------1 221 251 261 46//2.然后将第1不得到的结果与ta表进行等值连接：with ta as(select 22 c1, 'a' c2 from dual union allselect 25,'b' from dual union allselect 26,'c' from dual union allselect 46,'d' from dual), tb as(select 1 c3,'"22,25,26,46"' c4 from dual)select td.c3,ta.c2from ta,(select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5from tbconnect bylevel <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1) tdwhere ta.c1 = td.c5/C3 C2---------- --1 a1 b1 c1 d//3.使用wm_concat字符串连接函数，将第2步得到的结果连接起来：//这样就能得到我们的结果了：with ta as(select 22 c1, 'a' c2 from dual union allselect 25,'b' from dual union allselect 26,'c' from dual union allselect 46,'d' from dual), tb as(select 1 c3,'"22,25,26,46"' c4 from dual)select te.a tb_c3,wm_concat(te.b) ta_c2from (select td.c3 a,ta.c2 bfrom ta,(select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5from tbconnect bylevel <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1) tdwhere ta.c1 = td.c5) tegroup by a/TB_C3 TA_C2---------- --------------1 a,b,c,d//解法二：with ta as(select 22 c1, 'a' c2 from dual union allselect 25,'b' from dual union allselect 26,'c' from dual union allselect 46,'d' from dual), tb as(select 1 c3,'"22,25,26,46"' c4 from dual)select c3,max(substr(sys_connect_by_path(c2,','),2)) afrom (select c3,c2,rownum rn,rownum - 1 rmfrom (select a.c1,a.c2,b.c3,b.c4from ta a,tb b)where instr(c4,c1) > 0)start with rn=1connect by prior rn=rmgroup by c3/C3 A---------- ------------------1 a,b,c,d//小结：//对于这个问题，我是首先将其分解为几个小问题，然后再分别解决每一个小问题，//最后将小问题穿连起来，就可以解决最终问题了//所以，在遇到问题的时候，我们何不先将问题分解，然后逐步的去解决呢？

原帖：
　　http://topic.csdn.net/u/20110518/17/b6c9413b-7c60-4584-b2f2-ca6a8455669c.html?seed=1688559632&r=73385746#r_73385746