What is Pivoting insert in Oracle?

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));

Table created.

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));

Table created.

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