I need to create a oracle table1 from another table2 in oracle. where i need to insert the data as the column name of table2. example

Question

Example:

Table2 (original table)

custid   01-jan-2018   07-jan-2018  14-jan-2018  21-jan-2018
102      7               2            5            4

Need to create a table like below (Table1):

custid   date             data
102      01-jan-2018       7
102      07-jan-2018       2
102      14-jan-2018       5
102      21-jan-2018       4

Please advise how can I achieve/create table1 from table2.

Answer 1

Below are steps to achieve this scenario:

create table Table2
(custid number,
"01-jan-2018" number,
"07-jan-2018" number,
"14-jan-2018" number,
"21-jan-2018" number);

insert into Table2 values (102,7,2,5,4);

create table Table3
(custid number,
"date" date,
"data" number);

insert into Table3(custid,"date","data")  
(SELECT *
FROM Table2
UNPIVOT
INCLUDE NULLS
(DATA FOR COL IN
   (
     "01-jan-2018" ,"07-jan-2018","14-jan-2018","21-jan-2018"
   ) 
));

select * from table3;

102 01-JAN-18   7
102 07-JAN-18   2
102 14-JAN-18   5
102 21-JAN-18   4