“pivoting insert” test

what’s pivoting insert? follow the example and get a better understanding

1.prepare the data material

create table sales_input_data
(
product_id varchar2(10),custmer_id varchar2(10),week_st varchar2(15),
sales_sun number(4),sales_mon number(4),sales_tue number(4),
sales_wed number(4),sales_thu number(4),sales_fri number(4),sales_sat number(4)
);

SQL> desc sales_input_data;
Name Null?                 Type
———————  ——————————————–
PRODUCT_ID           VARCHAR2(10)
CUSTMER_ID           VARCHAR2(10)
WEEK_ST                   VARCHAR2(15)
SALES_SUN              NUMBER(4)
SALES_MON            NUMBER(4)
SALES_TUE              NUMBER(4)
SALES_WED             NUMBER(4)
SALES_THU              NUMBER(4)
SALES_FRI               NUMBER(4)
SALES_SAT               NUMBER(4)

insert into sales_input_data values(‘111′,’222′,’01-OCT-00’,100,200,300,400,500,600,700);
insert into sales_input_data values(‘222′,’333′,’08-OCT-00’,200,300,400,500,600,700,800);
insert into sales_input_data values(‘333′,’444′,’15-OCT-00’,300,400,500,600,700,800,900);

SQL> select * from sales_input_data;

PRODUCT_ID CUSTMER_ID WEEK_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT
———- ———- ————— ———- ———- ———- ———- ———- ———- ———-
111      222       01-OCT-00      100   200   300   400   500   600    700
222     333      08-OCT-00      200    300   400   500   600   700    800
333      444        15-OCT-00    300     400   500   600   700   800    900
create table sales (prod_id varchar2(10),cust_id varchar2(10),time_id varchar2(15),amount_sold number(4));

2.piece of information from the oracle online document

Pivoting Scenarios
A data warehouse can receive data from many different sources. Some of these source systems may not be relational databases and may store data in very different formats from the data warehouse. For example, suppose that you receive a set of sales records from a nonrelational database having the form:

product_id, custmer_id, weekly_start_date, sales_sun, sales_mon, sales_tue,sales_wed, sales_thu, sales_fri, sales_sat

The input table looks like the following:

SQL> select * from sales_input_data;

PRODUCT_ID CUSTMER_ID WEEK_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT
———- ———- ————— ———- ———- ———- ———- ———- ———- ———-
111      222    01-OCT-00   100   200   300   400   500   600    700
222     333    08-OCT-00   200   300   400   500   600   700   800
333     444     15-OCT-00   300   400   500   600   700   800   900

In your data warehouse, you would want to store the records in a more typical relational form in a fact table sales of the sh sample schema:

prod_id, cust_id, time_id, amount_sold
Note:

A number of constraints on the sales table have been disabled for purposes of this example, because the example ignores a number of table columns for the sake of brevity.
Thus, you need to build a transformation such that each record in the input stream must be converted into seven records for the data warehouse’s sales table. This operation is commonly referred to as pivoting, and Oracle Database offers several ways to do this.

3.do pivoting insert

insert all
into sales values (product_id,custmer_id,week_st,sales_sun)
into sales values (product_id,custmer_id,week_st,sales_mon)
into sales values (product_id,custmer_id,week_st,sales_tue)
into sales values (product_id,custmer_id,week_st,sales_wed)
into sales values (product_id,custmer_id,week_st,sales_thu)
into sales values (product_id,custmer_id,week_st,sales_fri)
into sales values (product_id,custmer_id,week_st,sales_sat)
select product_id,
custmer_id,
week_st,
sales_sun,
sales_mon,
sales_tue,
sales_wed,
sales_thu,
sales_fri,
sales_sat
from sales_input_data;
The result of the previous example will resemble the following:

SQL> select * from sales;

PROD_ID CUST_ID TIME_ID AMOUNT_SOLD
———- ———- ————— ———–
111   222 01-OCT-00 100
222   333 08-OCT-00 200
333   444 15-OCT-00 300
111   222 01-OCT-00 200
222   333 08-OCT-00 300
333   444 15-OCT-00 400
111    222 01-OCT-00 300
222   333 08-OCT-00 400
333   444 15-OCT-00 500
111    222 01-OCT-00 400
222   333 08-OCT-00 500
333   444 15-OCT-00 600
111    222 01-OCT-00 500
222    333 08-OCT-00 600
333    444 15-OCT-00 700
111    222 01-OCT-00 600
222   333 08-OCT-00 700
333   444 15-OCT-00 800
111    222 01-OCT-00 700
222   333 08-OCT-00 800
333   444 15-OCT-00 900
111    222 01-OCT-00 100
222   333 08-OCT-00 200
333   444 15-OCT-00 300
111    222 01-OCT-00 200
222   333 08-OCT-00 300
333   444 15-OCT-00 400
111    222 01-OCT-00 300
222    333 08-OCT-00 400
333    444 15-OCT-00 500
111    222 01-OCT-00 400
222   333 08-OCT-00 500
333   444 15-OCT-00 600
111    222 01-OCT-00 500
222   333 08-OCT-00 600
333   444 15-OCT-00 700
111    222 01-OCT-00 600
222   333 08-OCT-00 700
333   444 15-OCT-00 800
111   222 01-OCT-00 700
222  333 08-OCT-00 800
333  444 15-OCT-00 900