将逗号分隔为多个行- Oracle SQL

Question

我有张桌子

table1

TEST_TYPE                   H_LEVEL                     HADOOP
22RETGGEDGDD, RRMMNFNEDGDD  Control Directory 1         Interchange Control Header
22RETGGEDGDD, RRMMNFNEDGDD  Control Directory 2         Interchange Control Header
22RETGGEDGDD, RRMMNFNEDGDD  Control Directory 3         Interchange Control Header

我知道如何在列中分隔逗号分隔的值，但我不知道如何在复制所有其他列的同时将值分开，如下所示。

table2

TEST_TYPE           H_LEVEL                     HADOOP
22RETGGEDGDD        Control Directory 1         Interchange Control Header
RRMMNFNEDGDD        Control Directory 1         Interchange Control Header
22RETGGEDGDD        Control Directory 2         Interchange Control Header
RRMMNFNEDGDD        Control Directory 2         Interchange Control Header
22RETGGEDGDD        Control Directory 3         Interchange Control Header
RRMMNFNEDGDD        Control Directory 3         Interchange Control Header

如何在Oracle SQL中做到这一点？

Answer 1

这里有一个选项；您需要继续执行第6行。

SQL> with table1 (test_type, h_level, hadoop) as
  2    (select '22RETGGEDGDD, RRMMNFNEDGDD', 'Control Directory 1', 'Interchange Control Header' from dual
  3     union all
  4     select '22RETGGEDGDD, RRMMNFNEDGDD', 'Control Directory 2', 'Interchange Control Header' from dual
  5    )
  6  select trim(regexp_substr(test_type, '[^,]+', 1, column_value)) test_type,
  7         h_level,
  8         hadoop
  9  from table1 join table(cast(multiset(select level from dual
 10                                       connect by level <= regexp_count(test_type, ',') + 1
 11                                      ) as sys.odcinumberlist )) on 1 = 1
 12  order by 2, 1 desc, 3;

TEST_TYPE            H_LEVEL             HADOOP
-------------------- ------------------- --------------------------
22RETGGEDGDD         Control Directory 1 Interchange Control Header
RRMMNFNEDGDD         Control Directory 1 Interchange Control Header
22RETGGEDGDD         Control Directory 2 Interchange Control Header
RRMMNFNEDGDD         Control Directory 2 Interchange Control Header

SQL>