Pivoting INSERT - Oracle

Pivoting is an operation in which you need to build a transformation such that each record from any  input stream, such as, a  nonrelational database table, must be converted into multiple records for a more relational database table environment.

•Suppose you receive a set of  sales records from a nonrelational database table, SALES_SOURCE_DATA in the following format:

EMPLOYEE_ID,WEEK_ID,SALES_MON,SALES_TUE,SALES_WED, SALES_THUR,SALES_FRI

•You would want to store these records in the SALES_INFO table in a more typical relational format:

EMPLOYEE_ID, WEEK, SALES

•Using a pivoting INSERT, convert the set of  sales records from the  nonrelational database table to relational format.

INSERT ALL   INTO sales_info VALUES (employee_id,week_id,sales_MON)   INTO sales_info VALUES (employee_id,week_id,sales_TUE)   INTO sales_info VALUES (employee_id,week_id,sales_WED)   INTO sales_info VALUES (employee_id,week_id,sales_THUR)   INTO sales_info VALUES (employee_id,week_id, sales_FRI)   SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,          sales_WED, sales_THUR,sales_FRI   FROM sales_source_data;

5 rows created.

In the example in the slide, the sales data is received from the nonrelational database table SALES_SOURCE_DATA, which is the details of the sales performed by a sales representative on each day of a week, for a week with a particular week ID.

    DESC SALES_SOURCE_DATA

Name                      Null?                Type
EMPLOYEE_ID                             NUMBER(6)
WEEK_ID                                      NUMBER(2)
SALES_MON                                 NUMBER(8,2)
SALES_TUE                                  NUMBER(8,2)
SALES_WED                                 NUMBER(8,2)
SALES_THUR                               NUMBER(8,2)
SALES_FRI                                    NUMBER(8,2)

SQL> SELECT * FROM SALES_SOURCE_DATA;

EMPLOYEE_ID WEEK_ID SALES_MON SALES_TUE SALES_WED SALES_THUR SALES_FRI

176             6      2000     3000      4000        5000     6000

SQL> DESC SALES_INFO 

Name                      Null?                Type

EMPLOYEE_ID                             NUMBER(6)

WEEK                                            NUMBER(2)

SALES                                            NUMBER(8,2)

SQL> 

SELECT * FROM sales_info;

EMPLOYEE_ID       WEEK      SALES

176                6        2000

176                6        3000

176                6        4000

176                6        5000

176                6        6000

Observe in the preceding example that using a pivoting INSERT, one row from the SALES_SOURCE_DATA table is converted into five records for the relational table, SALES_INFO.