Tuesday, 21 August 2012

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

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...