Oracle SQL inserting multiple rows and splitting data from single foreign key-CodePudding

Trying to make an insert that gets its data from a table and splits it into two (or more) rows in another table while retaining the foreign key from the parent table.

For this example lets say parent table is Fruit and has columns:

fruit_id, num_fruit

The table I am inserting to is Fruit_Sub with columns:

Fruit_Sub_id, Fruit_id_FK, fruit_name, num_sub_fruit

Each row of fruit that is to be inserted into Fruit_sub knowing that from Fruit.num_fruit there is always 3 apples and the rest are bananas.

If Fruit looks like this:

fruit_id    num_fruit
----------------------
   1            5
   2            9
   3            4

I want to insert data into Fruit_sub like this:

fruit_sub_id    fruit_id_fk   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

So far all of my cracks at it aren't even recognizable as SQL. This one's just a bit beyond me.

CodePudding user response：

You could union two queries and then wrap with a row_number window function to generate the sub_id.

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

fiddle

CodePudding user response：

One of the options is to use Case expression with UNPIVOT

WITH    --  S a m p l e   D a t a :
    tbl AS
        (   Select 1 "FRUIT_ID", 5 "NUM_FRUIT" From Dual Union All
            Select 2 "FRUIT_ID", 9 "NUM_FRUIT" From Dual Union All
            Select 3 "FRUIT_ID", 4 "NUM_FRUIT" From Dual 
        )

--  M a i n   S Q L :
Select  ROWNUM "FRUIT_SUB_ID", FRUIT_ID_FK, FRUIT_NAME, 
        Case When FRUIT_NAME = 'apples' Then NUM_APPLES Else NUM_BANANAS End "NUM_SUB_FRUIT"
From    (   Select  FRUIT_ID "FRUIT_ID_FK", 'apples' "APPLES", 3 "NUM_APPLES", 'bananas' "BANANAS", NUM_FRUIT - 3 "NUM_BANANAS"
            From    tbl
        ) UNPIVOT ( FRUIT_NAME
                    FOR FRUIT IN(APPLES as 'apples', BANANAS as 'bananas')  )

/*    R e s u l t :
FRUIT_SUB_ID FRUIT_ID_FK 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  */

If there is a possibility that there is a row(s) with NUM_FRUIT less than 3 - you could handle it with Case expression too:

WITH    --  S a m p l e   D a t a :
    tbl AS
        (   Select 1 "FRUIT_ID", 5 "NUM_FRUIT" From Dual Union All
            Select 2 "FRUIT_ID", 2 "NUM_FRUIT" From Dual 
        )   
--  M a i n   S Q L :
Select  ROWNUM "FRUIT_SUB_ID", FRUIT_ID_FK, FRUIT_NAME, 
        Case When FRUIT_NAME = 'apples' Then NUM_APPLES Else NUM_BANANAS End "NUM_SUB_FRUIT"
From    (   Select  FRUIT_ID "FRUIT_ID_FK", 
                    'apples' "APPLES", 
                    Case When NUM_FRUIT >= 3 Then 3 Else NUM_FRUIT End "NUM_APPLES", 
                    'bananas' "BANANAS", 
                    Case When NUM_FRUIT >= 3 Then NUM_FRUIT - 3 Else 0 End "NUM_BANANAS"
            From    tbl
      ) UNPIVOT ( FRUIT_NAME
                  FOR FRUIT IN(APPLES as 'apples', BANANAS as 'bananas')  )
/*    R e s u l t :
FRUIT_SUB_ID FRUIT_ID_FK FRUIT_NAME NUM_SUB_FRUIT
------------ ----------- --------- -------------
           1           1 apples                3
           2           1 bananas               2
           3           2 apples                2
           4           2 bananas               0  */