2

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.

0
2
+25

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

1
  • I cannot use push_data_temp table now. I gave that for reference. i now need to insert in push_data_pivot using proc. – Velocity Nov 13 '20 at 15:41
0

I got the answer to this question.

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=412a6e27a22741ee1c31eee4a3f2bf3a

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.