Pivoting insert usage in Oracle

1. Standard Insert-single row insert

Syntax: INSERT into table [(Column1,column2,...)]   VALUE (value1,value2,...)    Example: INSERT into DEP (Dep_id,dep_name) VALUES (1, ' technical Department '); Note: Only one piece of data can be inserted using standard syntax, and data can only be inserted in one table 2, unconditional insert all-multi-table multi-row InsertGrammar:INSERT [All] [Condition_insert_clause][Insert_into_clause Values_clause] (subquery)Example:INSERT AllInto sal_history (emp_id,hire_date,salary) VALUES (empid,hiredate,sal)Into mgr_history (emp_id,manager_id,salary) VALUES (empid,hiredate,sal)SELECT employee_id empid,hire_date hiredate,salary sal,manager_id MgrFrom employeesWHERE employee_id>200; 3, conditional insertGrammar:INSERT [All | First]When condition then Insert_into_clause values_clause[when condition then] [insert_into_clause Values_clause]......[ELSE] [insert_into_clause Values_clause]subquery;Example:Insert AllWhen id>5 and into Z_test1 (ID, name) VALUES (id,name)When Id<>2 and into z_test2 (ID) VALUES (ID )else into Z_TEST3 values (name)select Id,name from Z_test;When using the ALL keyword, Oracle judges each condition from top to bottom, and executes the following into statement when the condition is metIn The example above, if Id=6 then inserts a record in the Z_test1, and also inserts a record in the Z_test2Note: When using the first keyword, Oracle will judge each condition from top to bottom, execute the following into statement when encountering the initial satisfaction, and interrupt the judging criteria, in the example above, if id=6, only inserts a single piece of data into the z_test1. 4, rotate Insert (pivoting insert)

Create TableSales_source_data (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));Insert  intoSales_source_dataValues(176,6, -, the,4000, the,6000);

 sql>  select  *  from   Sales_source_data; employee_id week_id sales_mon sales_tue sales_wed sales_thur sales_fri  --  ------------------------------------------------------------------ 176  6  2000.00  3000.00  4000.00  5000.00  6000.00  

Create Table  Number (6number (2Number (8), 2 ));

Looking at the table structure above, it is now time to convert the data in the Sales_source_data table into the Sales_info table, which requires the use of the rotate insert example as follows:

Insert  All intoSales_infoValues(Employee_id,week_id,sales_mon) intoSales_infoValues(Employee_id,week_id,sales_tue) intoSales_infoValues(employee_id,week_id,sales_wed) intoSales_infoValues(Employee_id,week_id,sales_thur) intoSales_infoValues(EMPLOYEE_ID,WEEK_ID,SALES_FRI)SelectEmployee_id,week_id,sales_mon,sales_tue,sales_wed,sales_thur,sales_fri fromSales_source_data;

Sql> Select *  fromsales_infor; emplyee_id WEEK SALES---------- ---- ----------       176    6    2000.00       176    6    3000.00       176    6    4000.00       176    6    5000.00       176    6    6000.00SQL> 

As can be seen from this example, the so-called rotary Insert is an unconditional insert all of a special application, but this application by the Oracle official, gave a pivoting insert name, that is, rotating insert "from:http:// Www.cnblogs.com/einyboy/archive/2012/08/06/2624886.html "

Pivoting insert usage in Oracle