CODEWITHSUNDEEP
sqloracle-databaseoracle11g
spiaire
spiaire

Reputation: 43

Oracle 11g split text column to rows

I have table:

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

How can i transform it to:

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

Upvotes: 2

Views: 7556

Answers (2)

Codo
Codo

Reputation: 78815

Using recursive common table expression, the same query looks like this:

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
),
split_first(id, val, rem) as
(
  select id,
    coalesce(substr(vals, 1, instr(vals, ',') - 1), vals) val,
    case when instr(vals, ',') > 0 then substr(vals, instr(vals, ',') + 1) end rem
  from sample_data
  union all
  select id,
    coalesce(substr(rem, 1, instr(rem, ',') - 1), rem) val,
    case when instr(rem, ',') > 0 then substr(rem, instr(rem, ',') + 1) end rem
  from split_first
  where rem is not null
)
select id, val from split_first
order by id;

Or a slightly different approach:

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
),
pos(id, seq, vals, sta, stp) as
(
  select id, 1, vals, 1, instr(vals, ',') from sample_data
  union all
  select id, seq + 1, vals, stp + 1, instr(vals, ',', stp + 1) from pos
  where stp > 0
)
select id, substr(vals, sta, case when stp > 0 then stp - sta else length(vals) end) from pos
order by id, seq;

Upvotes: 2

Codo
Codo

Reputation: 78815

Using the built-in XML functions, you can do it like that:

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;

Result:

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

Upvotes: 6

Related Questions