Preparation
* Define column names for pivot table
SQL>
SELECT LISTAGG (
'''' || JOB_ID || '''' || ' AS ' || '"' || JOB_TITLE || '"' || ', ')
WITHIN GROUP (ORDER BY JOB_TITLE)
FROM JOBS;
Pivot example
* Show that how many jobs are set to persons according to department names.
SQL>
SELECT *
FROM (SELECT JOB_ID, DEPARTMENT_NAME
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) PIVOT (
COUNT (JOB_ID)
FOR JOB_ID
IN ('AD_VP' AS "ADMIN",
'FI_ACCOUNT' AS "Accountant",
'AC_MGR' AS "Accounting Manager",
'AD_ASST' AS "Administration Assistant",
'FI_MGR' AS "Finance Manager",
'HR_REP' AS "Human Resources Representative",
'MK_MAN' AS "Marketing Manager",
'MK_REP' AS "Marketing Representative",
'AD_PRES' AS "Presidentt",
'IT_PROG' AS "Programmer",
'AC_ACCOUNT' AS "Public Accountant",
'PR_REP' AS "Public Relations Represent.",
'PU_CLERK' AS "Purchasing Clerk",
'PU_MAN' AS "Purchasing Manager",
'SA_MAN' AS "Sales Manager",
'SA_REP' AS "Sales Representative",
'SH_CLERK' AS "Shipping Clerk",
'ST_CLERK' AS "Stock Clerk",
'ST_MAN' AS "Stock Manager"))
ORDER BY DEPARTMENT_NAME DESC;
