PIVOTING-INSERT : 이거 신기하네요..ㅋㅋㅋ
공부를 너무 늦게 시작했나보다..이제야 이런 신세계를 알다니..ㅋㅋ
/* 테이블 생성 */
CREATE TABLE SALES
(
EMPLOYEE_ID NUMBER(6),
WEEK_ID NUMBER(2),
SALES_MON NUMBER(8,2),
SALES_TUE NUMBER(8,2),
SALES_WED NUMBER(8,2),
SALES_THU NUMBER(8,2),
SALES_FRI NUMBER(8,2)
);
COMMIT;
INSERT INTO SALES VALUES(1101,4,100,150,80,60,120);
INSERT INTO SALES VALUES(1102,5,300,300,230,120,150);
/* PIVOT 테이블 생성 */
CREATE TABLE SALES_DATA
(EMPLOYEE_ID NUMBER(6), WEEK_ID NUMBER(2), SALES NUMBER(8,2));
/* INSERT - 순식간에 10행 INSERT 완료 */
INSERT ALL
INTO SALES_DATA VALUES(EMPLOYEE_ID, WEEK_ID, SALES_MON)
INTO SALES_DATA VALUES(EMPLOYEE_ID, WEEK_ID, SALES_TUE)
INTO SALES_DATA VALUES(EMPLOYEE_ID, WEEK_ID, SALES_WED)
INTO SALES_DATA VALUES(EMPLOYEE_ID, WEEK_ID, SALES_THU)
INTO SALES_DATA VALUES(EMPLOYEE_ID, WEEK_ID, SALES_FRI)
SELECT EMPLOYEE_ID, WEEK_ID, SALES_MON, SALES_TUE, SALES_WED, SALES_THU, SALES_FRI
FROM SALES;
SELECT* FROM SALES_DATA;
-- FROM 알기쉽게 해설한 오라클 SQL & PL/SQL 저자 : 주종면 --