sql - Oracle 11g 将文本列拆分为行

Question

我有 table :

ID   |Values
-----+--------------------------------
1    |AB,AD
2    |AG, ... ,BD
3    |AV

我怎样才能将它转换成:

ID   |Value
-----+------
1    |AB
1    |AD
2    |AG
...  |...
2    |BD
3    |AV

Answer 1

使用内置的 XML 函数，您可以这样做:

with sample_data as
(
 select 1 id, 'AB,AD' vals from dual union all
 select 2, 'AG,AK,AJ,BA,BD' from dual union all
 select 3, 'AV' from dual
)
select id, cast(t.column_value.extract('//text()') as varchar2(10)) val
  from sample_data,
  table( xmlsequence( xmltype(
    '<x><x>' || replace(vals, ',', '</x><x>') || '</x></x>'
  ).extract('//x/*'))) t;

结果:

ID   VAL
--- -----
 1   AB
 1   AD
 2   AG
 2   AK
 2   AJ
 2   BA
 2   BD
 3   AV