Oracle Pivot Table: How to Convert Rows to Columns

How to Convert Rows to Columns in Oracle Using Pivot Tables

Topic Introduction: In this tutorial, we will walk through how to convert rows into columns in Oracle using Pivot Tables. This powerful technique is commonly used to summarize and organize data for better analysis and reporting. Oracle Pivot Tables allow you to rotate data, making it more readable by transforming row-based information into columns, often for aggregating data like sums, averages, or counts.

Example 1: Using Static Values for Pivoting

The first method involves using static values in the IN clause of the PIVOT operation. This is useful when you know the distinct values ahead of time (e.g., specific job roles or categories).

Here’s an example SQL query where we sum employee salaries by job ID and department, then pivot the data to show each job role as a column:

SELECT *
FROM (
    SELECT job_id, department_name, SUM(salary) AS salary
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    GROUP BY job_id, department_name
)
PIVOT (
    SUM(salary) AS salary
    FOR job_id IN (
        'AC_ACCOUNT' AS AC_ACCOUNT,
        'AC_MGR' AS AC_MGR,
        'AD_ASST' AS AD_ASST,
        'AD_PRES' AS AD_PRES,
        'AD_VP' AS AD_VP,
        'FI_ACCOUNT' AS FI_ACCOUNT,
        'FI_MGR' AS FI_MGR,
        'HR_REP' AS HR_REP,
        'IT_PROG' AS IT_PROG,
        'MK_MAN' AS MK_MAN,
        'MK_REP' AS MK_REP,
        'PR_REP' AS PR_REP,
        'PU_CLERK' AS PU_CLERK,
        'PU_MAN' AS PU_MAN,
        'SA_MAN' AS SA_MAN,
        'SA_REP' AS SA_REP,
        'SH_CLERK' AS SH_CLERK,
        'ST_CLERK' AS ST_CLERK,
        'ST_MAN' AS ST_MAN
    )
)
ORDER BY department_name;

In this example:

• The query groups employees by job_id and department_name, and sums up the salary for each group.
• The PIVOT clause then takes the distinct job IDs (e.g., 'AC_ACCOUNT', 'FI_MGR', etc.) and converts them into columns. The result is a report where each job role appears as a separate column with the total salary for each department.

Example 2: Using Dynamic Values for Pivoting

In some cases, the distinct values you need to pivot (such as job IDs) might not be known in advance. In these situations, you can use dynamic values by querying the distinct values directly from the database.

Here’s an example of how to achieve this using PIVOT XML, which allows for a dynamic list of job IDs:

SELECT *
FROM (
    SELECT job_id, department_name, SUM(salary) AS salary
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    GROUP BY job_id, department_name
)
PIVOT XML (
    SUM(salary) AS salary
    FOR job_id IN (SELECT DISTINCT job_id FROM employees)
)
ORDER BY department_name;

In this query:

• The PIVOT XML allows the dynamic retrieval of distinct job IDs using a subquery (SELECT DISTINCT job_id FROM employees).
• The result will display each distinct job_id as a separate column, with the summed salaries for each department.

Benefits of Using Pivot Tables in Oracle

• Data Organization: Pivot tables allow you to restructure and present data in a more understandable and insightful way by transforming rows into columns.
• Dynamic Reports: With the use of dynamic values in the PIVOT XML, you can create flexible and automated reports that adjust to changes in your data set without requiring manual updates.
• Advanced Aggregation: Pivot tables offer built-in aggregation functions like SUM(), COUNT(), AVG(), and more, which allow you to perform calculations directly within the query.

Conclusion

Oracle Pivot Tables are a powerful feature for transforming and summarizing data in a more readable format. Whether you're working with static values that you know beforehand or dynamic values pulled from your database, pivoting in Oracle can help streamline your reporting and data analysis.

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