I have a PL/SQL procedure which takes string at runtime which is comma separated,splits it and pushes into table.
Example of procedure and table is given here:
Procedure is push_data.
Current table where data is getting inserted is push_data_temp.
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fae31c11a4ad6205ebcaa418a0bffffd
Now as per requirement, i need to write a new procedure where logic would remain same of splitting data, but in the new table push_data_pivot(structure inside pivot), should display data pivoted in this format.
Ex: if currently data in push_data_temp is:
The data should go in push_data_pivot in the new procedure as:
The logic should remain same, just that the data getting inserted would go into columns instead of rows, populating id and value columns.
Here column **id**, is the unique identifier for the string passed in both tables. Basically, it is same as the one used in original table.
Was unable to update fiddle for push_data_pivot structure so mentioning the latest structure and data here:
PUSH_DATA_PIVOT :
create table PUSH_DATA_PIVOT
(
id_pk NUMBER,
id NUMBER,
label_id number,
label varchar2(4000),
value varchar2(4000)
);
insert into push_data_pivot values(1,null,'COL01',null);
insert into push_data_pivot values(1,null,'COL02',null);
insert into push_data_pivot values(1,null,'COL03',null);
insert into push_data_pivot values(1,null,'COL04',null);
insert into push_data_pivot values(1,null,'COL05',null);
insert into push_data_pivot values(1,null,'COL06',null);
insert into push_data_pivot values(1,null,'COL07',null);
insert into push_data_pivot values(1,null,'COL08',null);
insert into push_data_pivot values(1,null,'COL09',null);
insert into push_data_pivot values(1,null,'COL10',null);
insert into push_data_pivot values(1,null,'COL11',null);
insert into push_data_pivot values(1,null,'COL12',null);
insert into push_data_pivot values(1,null,'COL13',null);
insert into push_data_pivot values(1,null,'COL14',null);
The sample data is single string, for single label_id and corresponding set of column label values.However actual table would have multiple such strings inserted on basis of id column, same as original requirement. This is already handled in existing procedure using cursor.
**The only requirement is, to create new procedure with new destination table as push_data_pivot and data in unpivoted format. Split logic would be same as in existing procedure.**