Oracle Procedure alter pivoted data to unpivot and push into different destination

oraclepivotplsql

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:
enter image description here

The data should go in push_data_pivot in the new procedure as:
enter image description here

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.

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.

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.

Best Answer

Why do you not simply unpivot the hole table

SELECT * FROM push_data_temp;

ID_PK |  ID | COL1                         | COL2                                                                                                                                                                                                                                                           | COL3     | COL4       | COL5                | COL6                                 | COL7           | COL8 | COL9 | COL10 | COL11 | COL12    | COL13 | COL14
----: | --: | :--------------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :------- | :--------- | :------------------ | :----------------------------------- | :------------- | :--- | :--- | :---- | :---- | :------- | :---- | :----
 null | 110 | Project title afor BYU heads | "The values are,<br> "exactly" up to the requirement and analysis done by the team.<br>Also it is difficult to,<br> prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team" | "Active" | "Disabled" | "25 tonnes of fuel" | "www.examplesites.com/html.asp&net;" | "Apprehension" | null | null | null  | "25"  | "Stable" | null  | null 
 null | 111 | Project title for IT Heads   | "The values are,<br> "exactly" up to the requirement and analysis done by the team.<br>Also it is difficult to,<br> prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team" | "Active" | "Disabled" | "25 tonnes of fuel" | "www.examplesites.com/html.asp&net;" | "Apprehension" | null | null | null  | "25"  | "Stable" | null  | null 

create table PUSH_DATA_PIVOT
(
id_pk NUMBER,
  id NUMBER,
  label_id number,
  label varchar2(4000),
  value varchar2(4000)
);

INSERT INTO PUSH_DATA_PIVOT
SELECT 1,ID,1,label,value
FROM
(SELECT * FROM push_data_temp
UNPIVOT INCLUDE NULLS(
    value
    FOR label 
    IN (
        col1 AS 'COL01', 
        col2 AS 'COL02', 
        col3 AS 'COL03', 
        col4 AS 'col4', 
        col5 AS 'col5', 
        col6 AS 'COL06', 
        col7 AS 'COL07', 
        col8 AS 'COL08', 
        col9 AS 'COL09', 
        col10 AS 'COL10', 
        col11 AS 'COL11', 
        col12 AS 'COL12', 
        col13 AS 'COL13', 
        col14 AS 'COL14'
    )
)) t1;

28 rows affected

select * from push_data_pivot;

ID_PK |  ID | LABEL_ID | LABEL | VALUE                                                                                                                                                                                                                                                         
----: | --: | -------: | :---- | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    1 | 110 |        1 | COL01 | Project title afor BYU heads                                                                                                                                                                                                                                  
    1 | 110 |        1 | COL02 | "The values are,<br> "exactly" up to the requirement and analysis done by the team.<br>Also it is difficult to,<br> prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team"
    1 | 110 |        1 | COL03 | "Active"                                                                                                                                                                                                                                                      
    1 | 110 |        1 | col4  | "Disabled"                                                                                                                                                                                                                                                    
    1 | 110 |        1 | col5  | "25 tonnes of fuel"                                                                                                                                                                                                                                           
    1 | 110 |        1 | COL06 | "www.examplesites.com/html.asp&net;"                                                                                                                                                                                                                          
    1 | 110 |        1 | COL07 | "Apprehension"                                                                                                                                                                                                                                                
    1 | 110 |        1 | COL08 | null                                                                                                                                                                                                                                                          
    1 | 110 |        1 | COL09 | null                                                                                                                                                                                                                                                          
    1 | 110 |        1 | COL10 | null                                                                                                                                                                                                                                                          
    1 | 110 |        1 | COL11 | "25"                                                                                                                                                                                                                                                          
    1 | 110 |        1 | COL12 | "Stable"                                                                                                                                                                                                                                                      
    1 | 110 |        1 | COL13 | null                                                                                                                                                                                                                                                          
    1 | 110 |        1 | COL14 | null                                                                                                                                                                                                                                                          
    1 | 111 |        1 | COL01 | Project title for IT Heads                                                                                                                                                                                                                                    
    1 | 111 |        1 | COL02 | "The values are,<br> "exactly" up to the requirement and analysis done by the team.<br>Also it is difficult to,<br> prepare a scenario notwithstanding the fact it is difficult. This user story is going to be slightly complex however it is up to the team"
    1 | 111 |        1 | COL03 | "Active"                                                                                                                                                                                                                                                      
    1 | 111 |        1 | col4  | "Disabled"                                                                                                                                                                                                                                                    
    1 | 111 |        1 | col5  | "25 tonnes of fuel"                                                                                                                                                                                                                                           
    1 | 111 |        1 | COL06 | "www.examplesites.com/html.asp&net;"                                                                                                                                                                                                                          
    1 | 111 |        1 | COL07 | "Apprehension"                                                                                                                                                                                                                                                
    1 | 111 |        1 | COL08 | null                                                                                                                                                                                                                                                          
    1 | 111 |        1 | COL09 | null                                                                                                                                                                                                                                                          
    1 | 111 |        1 | COL10 | null                                                                                                                                                                                                                                                          
    1 | 111 |        1 | COL11 | "25"                                                                                                                                                                                                                                                          
    1 | 111 |        1 | COL12 | "Stable"                                                                                                                                                                                                                                                      
    1 | 111 |        1 | COL13 | null                                                                                                                                                                                                                                                          
    1 | 111 |        1 | COL14 | null

db<>fiddle here

Related Question