SQL Query

Displaying the sum total salaries of employees per department for a particular year

SQL> select *
2 from (select department_id, to_char(trunc(hire_date, 'YYYY'), 'YYYY')
hire_date, salary
3 from employee)
4 PIVOT (SUM(salary)
5 FOR (department_id, hire_date) IN
6 ((10, '2007') AS Accounting_2007,
7 (20, '2008') AS Payroll_2008,
8 (30, '2010') AS IT_2010
9 )
10 );
ACCOUNTING_2007                  PAYROLL_2008             IT_2010
—————————           ————————      ——————————————
190000                                                     90000                        370000
1 row selected.

Pivoting on and displaying multiple aggregate columns

SQL> select *
2 from (select department_id, hire_date, salary
3 from employee)
4 PIVOT (SUM(salary) AS sals,
5 MAX(hire_date) AS latest_hire
6 FOR department_id IN (10, 20, 30, NULL));
10_SALS  10_LATEST  20_SALS 20_LATEST 30_SALS 30_LATEST NULL_SALS NULL_LATE

370000 07-JUL-11 155000 06-MAR-09 370000 27-FEB-10 75000 14-SEP-05


Creating a table with pivoted data


SQL> CREATE TABLE pivoted_emp_data AS
2 select *
3 from (select department_id, hire_date, salary
4 from employee)
5 PIVOT (SUM(salary) sum_sals,
6 MAX(hire_date) latest_hire
7 FOR department_id IN (10 AS Acc, 20 AS Pay, 30 AS IT, NULL));

Using the UNPIVOT operator to turn rows into columns
SQL> select hire_date, salary
2 from pivoted_emp_data
3 UNPIVOT INCLUDE NULLS
4 ((hire_date, salary)
5 FOR department_id IN (
6 (acc_latest_hire, acc_sum_sals) AS 'Accounting',
7 (pay_latest_hire, pay_sum_sals) AS 'Payroll',
8 (it_latest_hire, it_sum_sals) AS 'IT',
9 (null_latest_hire, null_sum_sals) AS 'Unassigned'
10 ))
11 order by hire_date, salary;
HIRE_DATE SALARY
———————————————— ———————————
14-SEP-05 75000
06-MAR-09 155000
27-FEB-10 370000
07-JUL-11 370000
4 rows selected.

Log information for external table creation

CREATE TABLE statement
CREATE TABLE "SYS_SQLLDR_X_EXT_T"
(
"PATIENT_NAME" VARCHAR2(30),
"V_YYYY" VARCHAR2(255), "V_MM" VARCHAR2(255),
"V_DD" VARCHAR2(255), "V_HH" VARCHAR2(255),
"V_MI" VARCHAR2(255), "V_SS" VARCHAR2(255),
"SERVICE_DATE" VARCHAR(4000)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY MY_DIR
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'MY_DIR':'t.bad'
LOGFILE 't.log_xt' READSIZE 1048576
FIELDS TERMINATED BY "," LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
( "PATIENT_NAME" CHAR(255) TERMINATED BY ",",
"V_YYYY" CHAR(255) TERMINATED BY ",",
"V_MM" CHAR(255) TERMINATED BY ",",
"V_DD" CHAR(255) TERMINATED BY ",",
"V_HH" CHAR(255) TERMINATED BY ",",
"V_MI" CHAR(255) TERMINATED BY ",",
"V_SS" CHAR(255) TERMINATED BY ",",
"SERVICE_DATE" CHAR(255) TERMINATED BY ","
)
) location('t.dat')
)REJECT LIMIT UNLIMITED
INSERT AS SELECT statement
INSERT /*+ append */ INTO T
(
PATIENT_NAME,
SERVICE_DATE
)
SELECT
"PATIENT_NAME",
to_date("V_YYYY" || "V_MM" || "V_DD" || "V_HH" || "V_MI" || "V_SS" ,
'YYYYMMDDHH24MISS')
FROM "SYS_SQLLDR_X_EXT_T"