Split one row to many in same database table

Question

We have a requirement where we want to split one row to many rows ( in the same table ) based on some conditions.

Let's suppose we have this table :

ID	Value
1	V1
2	V2
3	V3

Requirement is,

• if ID=1, split this row into two more rows where IDs of new rows will be 4 and 5 and the value will be V1 (same as ID = 1 value) only.
• if ID=2, don't split.
• if ID=3, split this row into one more row where ID of the new row will be 6 and value will be V3 (same as ID = 3 value) only.

The final o/p will be :

ID	Value
1	V1
4	V1
5	V1
2	V2
3	V3
6	V3

I am looking out for some SQL script/Stored Proc that will help me in achieving the same.

Answer 1

You can generate the rows with a join and derived table . . . and then use union all to bring in the existing rows:

select id, value
from t
union all
select x.new_id, t.value
from t join
     (select 1 as old_id, 4 as new_id from dual union all
      select 1 as old_id, 5 as new_id from dual union all
      select 3 as old_id, 6 as new_id from dual 
     ) x
     on t.id = x.old_id;

If you just want to insert the values, use insert with the second query.

Answer 2

You can join your table with numbers as follows:

select case when t.id = 2 then t.id
            when t.id = 3 then t.id * lvl
            when t.id = 1 and lvl > 1 then lvl+2 
            else lvl 
      end as id, t.value 
  from your_table t
  cross join (select level as lvl from dual connect by level <=3)
where t.id = 1 or (t.id=2 and lvl=1) or (t.id = 3 and lvl <= 2)