- //A表:
- c1 c2
- 22 a
- 25 b
- 26 c
- 46 d
- //B表:
- c3 c4
- 1 “22,25,26,46,”
- //结果:
- tb_c3 ta_c2
- 1 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) c5
- from tb
- connect by
- level <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1;
- /
- C3 C5
- ---------- ----------------------
- 1 22
- 1 25
- 1 26
- 1 46
- //2.然后将第1不得到的结果与ta表进行等值连接:
- with ta as(
- select 22 c1, 'a' c2 from dual union all
- select 25,'b' from dual union all
- select 26,'c' from dual union all
- select 46,'d' from dual)
- , tb as(
- select 1 c3,'"22,25,26,46"' c4 from dual)
- select td.c3,ta.c2
- from ta,(
- select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5
- from tb
- connect by
- level <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1) td
- where ta.c1 = td.c5
- /
- C3 C2
- ---------- --
- 1 a
- 1 b
- 1 c
- 1 d
- //3.使用wm_concat字符串连接函数,将第2步得到的结果连接起来:
- //这样就能得到我们的结果了:
- with ta as(
- select 22 c1, 'a' c2 from dual union all
- select 25,'b' from dual union all
- select 26,'c' from dual union all
- select 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_c2
- from (
- select td.c3 a,ta.c2 b
- from ta,(
- select c3,regexp_substr(replace(c4,'"',''),'[^,]+',1,level) c5
- from tb
- connect by
- level <= length(replace(c4,'"','')) - length(replace(replace(c4,'"',''),',','')) + 1) td
- where ta.c1 = td.c5) te
- group by a
- /
- TB_C3 TA_C2
- ---------- --------------
- 1 a,b,c,d
- //解法二:
- with ta as(
- select 22 c1, 'a' c2 from dual union all
- select 25,'b' from dual union all
- select 26,'c' from dual union all
- select 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)) a
- from (
- select c3,c2,rownum rn,rownum - 1 rm
- from (
- select a.c1,a.c2,b.c3,b.c4
- from ta a,tb b)
- where instr(c4,c1) > 0)
- start with rn=1
- connect by prior rn=rm
- group by c3
- /
- C3 A
- ---------- ------------------
- 1 a,b,c,d
oracle 一行列转换问题
最新推荐文章于 2024-10-02 11:52:29 发布