Generate parent record from child records

Question

I have the following situation.

Im getting data from an external system in the form

UserId  Group  Value
1       Abc    .....
1       Abc    .....
1       Bcd    .....
2       Abc    .....

I need to massage this data into my schema, i.e.

Parent Table
ID    UserID    Group    Name
1     1         Abc      User 1 - Abc data
2     1         Bcd      User 1 - Bcd data
3     2         Abc      User 2 - Abc data

Child Table
ParentID  Value
1         .....
1         .....
2         .....
3         .....

So there is a 1 to 1 between the child data and the data from the external system. The issue is that I am starting with the child data and need to write a query that can create a parent record based on distinct combinations of UserID and Group

Answer 1

need to write a query that can create a parent record based on distinct combinations of UserID and Group

insert into parent(UserID, Group, Name) 
select distinct 
UserID,  
Group, UserID, 
'User ' || UserID || ' - ' || Group || '  data'
from raw_input_data;

Answer 2

insert parent (userId, group, name)
select distinct userId, group, concat("user ", userId, " - ", group, " data")
from input_data;

(where it has an autoincremented ID)

then:

insert child (parentId, value)
select p.parentId, d.value
from parent p
inner join input_data d on p.user_id = d.user_id and p.group = d.group;