e.g. pivot clause

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;