Oracle 正则表达式行转列

原始的数据：

aa,bb,cc,dd

ee,ff,gg,hh

想要得到的结果：

aa

bb

cc

dd

...

先弄一条试试：

SQL> with t as(

      select 1 id,'aa,bb,cc,dd' names from dual
    )
    select  id,REGEXP_SUBSTR(names, '[^,]+', 1, level) name,
     'REGEXP_SUBSTR('''||names|| ',''[^,]+'', 1, '||level||')' exec_s  from t
    connect by level <=regexp_count(names,',')+1
    order by name;
  ID NAME   EXEC_S
---- ------ --------------------------------------------
   1 aa     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 1)
   1 bb     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 2)
   1 cc     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
   1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
已用时间:  00: 00: 00.01

这么写发现结果集不对：

SQL> with t as(
       select 1 id,'aa,bb,cc,dd' names from dual
       union all
       select 2 id,'ee,ff,gg,hh' names from dual
     )
     select  id,REGEXP_SUBSTR(names, '[^,]+', 1, level) name,
      'REGEXP_SUBSTR('''||names|| ',''[^,]+'', 1, '||level||')' exec_s from t
     connect by level <=regexp_count(names,',')+1
     order by name;
 ID NAME   EXEC_S
--- ------ ----------------------------------------------
  1 aa     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 1)
  1 bb     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 2)
  1 bb     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 2)
  1 cc     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
  1 cc     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
  1 cc     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
  1 cc     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
  1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
  1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
  1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
  1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
  1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
  1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
  1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
  1 dd     REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
  2 ee     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 1)
  2 ff     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 2)
  2 ff     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 2)
  2 gg     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
  2 gg     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
  2 gg     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
  2 gg     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
  2 hh     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
  2 hh     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
  2 hh     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
  2 hh     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
  2 hh     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
  2 hh     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
  2 hh     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
  2 hh     REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
已选择30行。

已用时间:  00: 00: 00.06

原因是多行的话产生了上下级关系：

SQL> with t as(
      select 1 id,'aa,bb,cc,dd' names from dual
      union all
      select 2 id,'ee,ff,gg,hh' names from dual
   ) select level,names,sys_connect_by_path(id,'--') as full_path,
     count(1) over(partition by names,level) as cnt
     from t
     connect by level <= regexp_count(names,',')+1
   order by names,level;
     LEVEL NAMES       FULL_PATH                   CNT
---------- ----------- -------------------- ----------
         1 aa,bb,cc,dd --1                           1
         2 aa,bb,cc,dd --2--1                        2
         2 aa,bb,cc,dd --1--1                        2
         3 aa,bb,cc,dd --1--2--1                     4
         3 aa,bb,cc,dd --1--1--1                     4
         3 aa,bb,cc,dd --2--2--1                     4
         3 aa,bb,cc,dd --2--1--1                     4
         4 aa,bb,cc,dd --1--1--2--1                  8
         4 aa,bb,cc,dd --1--1--1--1                  8
         4 aa,bb,cc,dd --2--2--2--1                  8
         4 aa,bb,cc,dd --2--2--1--1                  8
         4 aa,bb,cc,dd --2--1--2--1                  8
         4 aa,bb,cc,dd --2--1--1--1                  8
         4 aa,bb,cc,dd --1--2--2--1                  8
         4 aa,bb,cc,dd --1--2--1--1                  8
         1 ee,ff,gg,hh --2                           1
         2 ee,ff,gg,hh --1--2                        2
         2 ee,ff,gg,hh --2--2                        2
         3 ee,ff,gg,hh --2--2--2                     4
         3 ee,ff,gg,hh --2--1--2                     4
         3 ee,ff,gg,hh --1--2--2                     4
         3 ee,ff,gg,hh --1--1--2                     4
         4 ee,ff,gg,hh --1--1--1--2                  8
         4 ee,ff,gg,hh --1--2--1--2                  8
         4 ee,ff,gg,hh --2--2--2--2                  8
         4 ee,ff,gg,hh --1--2--2--2                  8
         4 ee,ff,gg,hh --2--1--2--2                  8
         4 ee,ff,gg,hh --2--1--1--2                  8
         4 ee,ff,gg,hh --2--2--1--2                  8
         4 ee,ff,gg,hh --1--1--2--2                  8
只限制本行拆分：        

SQL> with t as(                                                       
        select 1 id,'aa,bb,cc,dd' names from dual                  
        union all                                                  
        select 2 id,'ee,ff,gg,hh' names from dual                  
      )                                                            
      select level,names,sys_connect_by_path(id,'--') as full_path,
    count(1) over(partition by names,level) as cnt from t          
      connect by prior names=names                                 
      and level<=regexp_count(names,',')+1                         
      and prior SYS_GUID() is not null;                                                    
     LEVEL NAMES       FULL_PATH                   CNT                
---------- ----------- -------------------- ----------                
         1 aa,bb,cc,dd --1                           1                
         2 aa,bb,cc,dd --1--1                        1                
         3 aa,bb,cc,dd --1--1--1                     1                
         4 aa,bb,cc,dd --1--1--1--1                  1                
         1 ee,ff,gg,hh --2                           1                
         2 ee,ff,gg,hh --2--2                        1                
         3 ee,ff,gg,hh --2--2--2                     1                
         4 ee,ff,gg,hh --2--2--2--2                  1                

改写：

SQL> with t as(
      select 1 id,'aa,bb,cc,dd' names from dual
      union all
      select 2 id,'ee,ff,gg,hh' names from dual
    )
    select  id,REGEXP_SUBSTR(names, '[^,]+', 1, level) name,
     'REGEXP_SUBSTR('''||names|| ',''[^,]+'', 1, '||level||')' exec_s from t
    connect by prior names=names
    and level<=regexp_count(names,',')+1
    and prior SYS_GUID() is not null
    order by name;
  ID NAME    EXEC_S
---- ------- ---------------------------------------------
   1 aa      REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 1)
   1 bb      REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 2)
   1 cc      REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 3)
   1 dd      REGEXP_SUBSTR('aa,bb,cc,dd,'[^,]+', 1, 4)
   2 ee      REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 1)
   2 ff      REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 2)
   2 gg      REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 3)
   2 hh      REGEXP_SUBSTR('ee,ff,gg,hh,'[^,]+', 1, 4)
已选择8行。
已用时间:  00: 00: 00.05