In-depth study of Oracle rotating insert statements

Oracle rotating insert statements may be unfamiliar to many new users who are new to Oracle. The following describes how to implement Oracle rotating insert statements for your reference.

The Oracle multi-table insert statement can also be used to rotate the Oracleinsert statement, that is, to convert the values of different columns in each row of the table into different row values in another table.
For example:
Create table sales table (
Id number primary key,
JAN number,
FEB number,
MAR number,
APR number
)

SQL> insert into sales table values (1,100,200,300,400 );

1 row created.

SQL> insert into sales table values (2,110,120,130,140 );

1 row created.

SQL> select * from sales table;

ID JAN FEB MAR APR
--------------------------------------------------
1 100 200 300 400
2 110 120 130 140

Create table monthly sales statistics table
(Numeric,
Month nvarchar2 (10 ),
Average monthly sales numeric,
Monthly sales numeric
);

Insert statement rotation:
SQL> insert all
2 into monthly sales statistical table values (1, 'August 1', avg1, sum1)
3 into monthly sales statistical table values (2, 'August 1', avg2, sum2)
4 into monthly sales statistical table values (3, 'August 1', avg3, sum3)
5 into monthly sales statistical table values (4, 'August 1', avg4, sum4)
6 select avg (JAN) avg1, sum (JAN) sum1,
7 avg (FEB) avg2, sum (FEB) sum2,
8 avg (MAR) avg3, sum (MAR) sum3,
9 avg (APR) avg4, sum (APR) sum4
10 from sales table;

4 rows created.

SQL> select * from monthly sales statistics table;

No. Monthly/monthly average sales monthly sales
----------------------------------------------------
January 10 210
February 16 0 320
March 21 430
4 April 27 0 540
The source data is as follows:
ID JAN FEB MAR APR
--------------------------------------------------
1 100 200 300 400
2 110 120 130 140
 

How to Implement Oracle fuzzy search

Statement of Oracle paging Query

Oracle condition branch statement example

Statement of Oracle cyclic statements

Oracle user syntax Modification