SQL - Need to populate sequences for records

Question

I have the below table (PART_MAP).

PART    | OFFER     |
--------+------------
part1   | offer01   |
part1   | offer02   |
part1   | offer03   |
part1   | offer04   |
part1   | offer05   |
part2   | offer06   |
part2   | offer07   |
part2   | offer08   |
part2   | offer09   |
part2   | offer10   |
part3   | offer11   |
part3   | offer12   |
part3   | offer13   |
part3   | offer14   |
part3   | offer15   |
part3   | offer16   |
...
part100 | offer1007 |
part100 | offer1008 |
part100 | offer1009 |
part100 | offer1110 |

Using thie table, i need to create inserts in another table (PART_MAP_SET) like this

SERIALNUM  | GRP         | PART    | OFFER     |
------------------------------------------------
0          | part1_grp   | part1   | offer01   | 
1          | part1_grp   | part1   | offer02   | 
2          | part1_grp   | part1   | offer03   | 
3          | part1_grp   | part1   | offer04   | 
4          | part1_grp   | part1   | offer05   | 
0          | part2_grp   | part2   | offer06   | 
1          | part2_grp   | part2   | offer07   | 
2          | part2_grp   | part2   | offer08   | 
3          | part2_grp   | part2   | offer09   | 
4          | part2_grp   | part2   | offer10   | 
0          | part3_grp   | part3   | offer11   | 
1          | part3_grp   | part3   | offer12   | 
2          | part3_grp   | part3   | offer13   | 
3          | part3_grp   | part3   | offer14   | 
4          | part3_grp   | part3   | offer15   | 
5          | part3_grp   | part3   | offer16   | 
...
0          | part100_grp | part100 | offer1007 | 
1          | part100_grp | part100 | offer1008 | 
2          | part100_grp | part100 | offer1009 | 
3          | part100_grp | part100 | offer1110 | 

I am using a unix shell script to invoke sqlplus on oracle. To minimize the number of times i need to connect to the DB, i am trying to create the insert statements as a spooled output from one query and am then running that spooled output as a script.

I am spooling the output of the below query

select 'insert into part_map_set 
(serialnum, grp, part, offer) 
values
(' || XXXX
   ||', '''
   || part ||'_grp'', '''
   || part 
   || ''', '''
   || offer
   || ''');' 
from (part_map); 

I am having trouble with the XXXX part. If i am able to populate it, my spooled output would be

insert into part_map_set (serialnum, grp, part, offer) values (0, 'part1_grp', 'part1', 'offer01');
insert into part_map_set (serialnum, grp, part, offer) values (1, 'part1_grp', 'part1', 'offer02');
...
insert into part_map_set (serialnum, grp, part, offer) values (0, 'part2_grp', 'part2', 'offer06');
...

Any suggestions how can i generate that sequence per partner from 0 to N.

My last resort would be to create 100 temporary sequences (1 per partner) and then fetch the sequences. But i am not going there yet :)

Below may help you setup:

Create table part_map (part varchar2(20), offer varchar2(20));
Create table part_map_set (serialnum number(4), grp varchar2(20), part varchar2(20), offer varchar2(20));
insert into part_map (part, offer) values ('part1', 'offer01');
insert into part_map (part, offer) values ('part1', 'offer02');
insert into part_map (part, offer) values ('part1', 'offer03');
insert into part_map (part, offer) values ('part1', 'offer04');
insert into part_map (part, offer) values ('part1', 'offer05');
insert into part_map (part, offer) values ('part2', 'offer06');
insert into part_map (part, offer) values ('part2', 'offer07');
insert into part_map (part, offer) values ('part2', 'offer08');
insert into part_map (part, offer) values ('part2', 'offer09');
insert into part_map (part, offer) values ('part2', 'offer10');
insert into part_map (part, offer) values ('part3', 'offer11');
insert into part_map (part, offer) values ('part3', 'offer12');
insert into part_map (part, offer) values ('part3', 'offer13');
insert into part_map (part, offer) values ('part3', 'offer14');
insert into part_map (part, offer) values ('part3', 'offer15');
insert into part_map (part, offer) values ('part3', 'offer16');
insert into part_map (part, offer) values ('part100', 'offer1007');
insert into part_map (part, offer) values ('part100', 'offer1008');
insert into part_map (part, offer) values ('part100', 'offer1009');
insert into part_map (part, offer) values ('part100', 'offer1110');

Answer 1

For the XXXX part, I think you want row_number():

select 'insert into part_map_set 
(serialnum, grp, part, offer) 
values
(' || row_number() over (partition by part order by part)
   ||', '''
   || part ||'_grp'', '''
   || part 
   || ''', '''
   || offer
   || ''');' 
from part_map; 

There may be other reasons why you are generating the code this way, but you can run the entire statement as a single insert:

insert into part_map_set (serialnum, grp, part, offer) 
    select row_number() over (partition by part order by part),
           part ||'_grp', part,  offer
    from part_map;

This should be much faster than a zillion separate insert statements.