Pivoting insert: In your Datawarehouse we come across situations where non-relational data has to be stored in a relational format .Here we can use the pivoting insert statement. When we have data as empid,weekid,sales_mon,sales_tues,sales_wed,...etc. We can use in a more relational format in a table as empid,week,sales columns. So pivoting is an operation in which one has to build a transformation such that each record from any input stream, such as a non-relational database table, must be converted into multiple records for a more relational database format.
EX: We have source table as sales_source
SQL> create table sales_source (empno number(5), weekid number(2),sales_m number(8,2),sales_tu number(8,2),sales_w number(8,2),sales_th number(8,2), sales_f number(8,2));
In your data warehouse, you would want to store the records in a more typical relational form in a fact table sales_info
SQL> create table sales_info (empid number(6), week number(2),SALES NUMBER(8,2));
SQL> insert into sales_source values(176,6,2000,3000,1000,5000,6000);
1 row created.
SQL> insert all into sales_info values(employid,weekid,sales_mon)
into sales_info values(employid,weekid,sales_tu)
into sales_info values(employid,weekid,sales_wed)
into sales_info values(employid,weekid,sales_th)
into sales_info values(employid,weekid,sales_f)
select empno employid,weekid weekid,sales_m sales_mon,
sales_tu sales_tu,sales_w sales_wed,sales_th sales_th,sales_f sales_f from sales_source;
5 rows created.
SQL> select * from sales_info;
EMPID WEEK SALES
---------- ---------- ----------
176 6 2000
176 6 3000
176 6 1000
176 6 5000
176 6 6000