Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table fruit (
  fruit_id integer, 
  num_fruit integer
  );
insert into fruit values (1, 5);
1 rows affected

insert into fruit values (2, 9);
1 rows affected

insert into fruit values (3, 4);
1 rows affected

select 
  row_number() over (order by fruit_id, fruit_name) as fruit_sub_id, 
  fruit_id, 
  fruit_name, 
  num_sub_fruit
from (
select 
 fruit_id, 
 'apples' as fruit_name, 
 3 as num_sub_fruit
from fruit
union
select 
 fruit_id, 
 'bananas', 
 num_fruit - 3 
from fruit
)z
order by 1,2,3


FRUIT_SUB_ID
FRUIT_ID
FRUIT_NAME
NUM_SUB_FRUIT


1
1
apples
3

2
1
bananas
2

3
2
apples
3

4
2
bananas
6

5
3
apples
3

6
3
bananas
1