Skip to Main Content

How to load data from staging table using unpivoting the data into the main table

Albert ChaoSep 2 2022 — edited Sep 2 2022

CREATE TABLE staging (
col1 number --auto generated
col2 VARCHAR2(10),
col3 VARCHAR2(10),
col4 VARCHAR2(10)
);

INSERT INTO staging VALUES(1,'e_id','e_name','e_addrs');
INSERT INTO staging VALUES(2,'1','A',NULL);
INSERT INTO staging VALUES(3,'2','B',NULL);

CREATE TABLE main_table (
seq_id number --auto generated
e_id VARCHAR2(10),
col_name VARCHAR2(10),
col_value VARCHAR2(10)
);

Oracle 18c - SQL Developer

I have one excel which contains data that I need to load into the staging table and then to the main table. A staging table will always contain the first row as the header of the excel and data will be from the 2nd row.
Expected output:
seq_id column would be auto - generated hence writeen expected output only for required column

+------+----------+-----------+
| e_id | col_name | col_value |
+------+----------+-----------+
| 1 | e_id | 1 |
| 1 | e_name | A |
| 2 | e_id | 2 |
| 2 | e_name | B |
+------+----------+-----------+

Logic: Need to check col1 of the staging table if it is e_id will populate value in main table in column e_id then will populate the first row of the staging table which is the column name i.e e_id in the main table column col_name and value will populate in col_value.
How it can be done either using sql or plsql? Any expert advice would be helpful.

Also, asked on Stack overflow

This post has been answered by Frank Kulash on Sep 2 2022
Jump to Answer
Comments
Post Details
Added on Sep 2 2022
8 comments
82 views