Oracle Pivot Table: How to Convert Rows to Columns

Topic Introduction: In this tutorial, we will show Oracle Pivot Table: How to Convert Rows to Columns.

Example with Static Value

SELECT *

    FROM (  SELECT job_id, department_name, SUM (salary) salary

              FROM employees e, departments d

             WHERE e.department_id = d.department_id

          GROUP BY job_id, department_name)

         PIVOT

             (SUM (salary)

             salary

             FOR job_id

             IN ('AC_ACCOUNT' AC_ACCOUNT,

                'AC_MGR' AC_MGR,

                'AD_ASST' AD_ASST,

                'AD_PRES' AD_PRES,

                'AD_VP' AD_VP,

                'FI_ACCOUNT' FI_ACCOUNT,

                'FI_MGR' FI_MGR,

                'HR_REP' HR_REP,

                'IT_PROG' IT_PROG,

                'MK_MAN' MK_MAN,

                'MK_REP' MK_REP,

                'PR_REP' PR_REP,

                'PU_CLERK' PU_CLERK,

                'PU_MAN' PU_MAN,

                'SA_MAN' SA_MAN,

                'SA_REP' SA_REP,

                'SH_CLERK' SH_CLERK,

                'ST_CLERK' ST_CLERK,

                'ST_MAN' ST_MAN))

ORDER BY department_name;

Example with Dynamic Value

  SELECT *

    FROM (  SELECT job_id, department_name, SUM (salary) salary

              FROM employees e, departments d

             WHERE e.department_id = d.department_id

          GROUP BY job_id, department_name)

         PIVOT XML

             (SUM (salary) salary

             FOR job_id

             IN (SELECT DISTINCT job_id

                   FROM employees))

ORDER BY department_name;

***Thanks for visiting my blog. My Youtube channel is Oracle School BD. You can visit here to see video tutorials.***