ORACLE之pivoting insert等

SQL> insert all
2 into sal_history values(empid,hiredate,sal)
3 into mgr_history values(empid,mgr,sal)
4 select employee_id empid,hire_date hiredate,salary sal,manager_id mgr
5 from employees
6 where employee_id>200;

8 rows created.
SQL> select employee_id from sal_history
2 union all
3 select employee_id from mgr_history
EMPLOYEE_ID
-----------
201
202
205
206
201
202
205
206

-- 有条件的insert all
insert all
when sal>10000 then
into sal_history values (empid,hiredate,sal)
when mgr>200 then
into mgr_history values (empid,mgr,sal)
select employee_id empid,hire_date hiredate,salary sal,manager_id mgr
from employees
where employee_id>200;

-- insert first是符合第一个的记录不再应用到下面的条件判断
insert first
when sal >25000 then
into special_sal values(deptid,sal)
when hiredate like ('%00%') then
into hiredate_history_00 values (deptid,hiredate)
when hiredate like ('%99%') then
into hiredate_history_99 values(deptid,hiredate)
else
into hiredate_history values(deptid,hiredate)
select department_id deptid,sum(salary) sal,
max(hire_date) hiredate
from employees
group by department_id;

-- 什么是pivoting insert
create table sales_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 into sales_source_data values (176,6,2000,3000,4000,5000,6000);

create table sales_info (
employee_id number(6),
week number(2),
sales number(8,2)
);
-- 现在要将上表的数据转换到下表中，
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;