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:
•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.
Name Null? Type
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
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.