Oracle 展开列和值

嗨，

我正在使用目标系统供应商提供的迁移工具将数据迁移到系统中。
该工具使用预定义的架构，我们以非规范化csv格式获取数据。所以我们需要转变
数据进入迁移架构。
所以我这里有一张表

create table t ( ID int, Prop1 varchar2(30), Prop2 varchar2(30), Prop3 varchar2(30), Prop4 varchar2(30) );

里面有这些数据

insert into t values ( 12345, 'abc', 'abc', 'abc%20%def', 'def' );

我需要将其转换为 :( Prop3中的值在 '% 20% 上分裂)

ID  PropName  Value
12345  Prop1   abc
12345  Prop2   abc
12345  Prop3   abc
12345  Prop3   def
12345  Prop4   def

今天我使用:

CURSOR cur_temp
IS
SELECT *
    FROM t UNPIVOT (PROPVALUE FOR PROPNAME IN(
    PROP1,PROP2,PROP3,PROP4));

然后，我将其批量收集到表格类型中
使用我在此站点上找到的这个不错的查询，使用PL/SQL处理它

    (select substr (x,
      instr (x, %20%, 1, level ) + 4,
      instr (x, %20%, 1, level+1) - instr (x, %20%, 1, level) - 4)
      as IBA_SINGLE_VAL
    from (select %20%||l_org_att_wtpart(indx).STRINGVALUE||%20% as x from dual)
    connect by instr (x, %20%, 1, level+1) > 0)

然后我使用FORALL插入值。

当前的解决方案有效，但尚未在较大的数据集上进行测试。在即将到来的迁移中，我们有一个数据
设置table t将具有大约2个miljon记录和20个prop列的位置，因此我有点担心性能。
我知道您明确的建议是首先尝试使用单个SQL语句，如果失败，请使用PL/SQL。

这可能在SQL中做到吗？

谢谢
安德斯

当然，在一份声明中是可能的。您需要做的就是将未旋转的数据传递给经过调整的csv到行解决方案。查找 % 20% 而不是逗号。

下面是一个使用正则表达式的例子:

create table t ( 
  ID int, Prop1 varchar2(30), Prop2 varchar2(30), 
  Prop3 varchar2(30), Prop4 varchar2(30) 
);
insert into t values ( 12345, 'abc', 'abc', 'abc%20%def', 'def' );

with rws as (
  select level x from dual
  connect by level <= 10 -- ensure >= number of %20% occurrences
), unpiv as (
  select *
  from   t unpivot (
    propvalue for propname in(prop1,prop2,prop3,prop4)
  )
)
  select regexp_count(propvalue, '%20%'), 
         regexp_substr(propvalue, '[^(%20%)]+', 1, x) val,
         unpiv.* 
  from   unpiv
  join   rws
  on     regexp_count(propvalue, '%20%') + 1 >= x 
  order  by id, propname, val;

REGEXP_COUNT(PROPVALUE,'%20%')   VAL   ID      PROPNAME   PROPVALUE    
                               0 abc     12345 PROP1      abc          
                               0 abc     12345 PROP2      abc          
                               1 abc     12345 PROP3      abc%20%def   
                               1 def     12345 PROP3      abc%20%def   
                               0 def     12345 PROP4      def

您可以在此站点上找到许多其他csv到行的示例。