1. Introduction: What is Transposing Rows into Columns?

When you transpose rows into columns, you’re converting row values into multiple columns.

This is often needed in reporting, data aggregation, or visualization where you want each unique value in a column (like department or month) to become a separate column.

Example Input Table

Let’s take an example table named EMP_SALARY:

EMP_ID	DEPT	SALARY
101	HR	60000
102	IT	80000
103	HR	65000
104	SALES	70000
105	IT	75000

We want to show departments as columns with average salaries:

METRIC	HR	IT	SALES
AVG_SAL	62500	77500	70000

---

2. Transposing Rows into Columns Using CASE Expression

The CASE expression method is the classic SQL approach before PIVOT was introduced.

It gives you full control and works in Oracle 8i, 9i, and above — so it’s useful for backward compatibility.

Example Query

SELECT
    'AVG_SAL' AS metric,
    AVG(CASE WHEN dept = 'HR' THEN salary END)   AS HR,
    AVG(CASE WHEN dept = 'IT' THEN salary END)   AS IT,
    AVG(CASE WHEN dept = 'SALES' THEN salary END) AS SALES
FROM emp_salary;

Explanation

1. CASE WHEN dept = 'HR' THEN salary END → selects salary for HR department, else NULL.
2. AVG() aggregates salaries only for HR rows.
3. Similar logic applies for IT and SALES.
4. Each CASE creates a new column.

Output

METRIC	HR	IT	SALES
AVG_SAL	62500	77500	70000

---

3. Transposing Rows into Columns Without CASE (Using PIVOT)

From Oracle 11g onwards, Oracle introduced the PIVOT operator, which makes this process much easier and cleaner.

Example Query Using PIVOT

SELECT *
FROM (
    SELECT dept, salary FROM emp_salary
)
PIVOT (
    AVG(salary)
    FOR dept IN ('HR' AS HR, 'IT' AS IT, 'SALES' AS SALES)
);

Explanation

1. Inner Query:
   Selects the source columns to be pivoted — dept and salary.
2. PIVOT Clause:
   • AVG(salary) → aggregate function used on each pivoted column.
   • FOR dept IN ('HR' AS HR, 'IT' AS IT, 'SALES' AS SALES) → converts department names into column headers.
3. Output:

HR	IT	SALES
62500	77500	70000

Notes

• Each value in the IN list becomes a column name.
• You can use any aggregate function: SUM, MAX, COUNT, etc.

---

4. Handling Unknown or Dynamic Columns

If your departments (or column names) are not fixed, you cannot hardcode them in the PIVOT clause.

In that case, you need to use Dynamic SQL (PL/SQL) to generate the column list at runtime.

Example: Dynamic PIVOT in Oracle

DECLARE
    sql_query VARCHAR2(1000);
    cols VARCHAR2(500);
BEGIN
    -- Step 1: Generate list of unique department names
    SELECT LISTAGG( '''' || dept || ''' AS "' || dept || '"', ',')
           WITHIN GROUP (ORDER BY dept)
    INTO cols
    FROM (SELECT DISTINCT dept FROM emp_salary);

    -- Step 2: Construct dynamic PIVOT SQL
    sql_query := 'SELECT * FROM (
                      SELECT dept, salary FROM emp_salary
                  )
                  PIVOT (
                      AVG(salary)
                      FOR dept IN (' || cols || ')
                  )';

    -- Step 3: Execute the SQL
    EXECUTE IMMEDIATE sql_query;
END;
/

Explanation

1. LISTAGG dynamically builds the list 'HR' AS "HR", 'IT' AS "IT", ...
2. sql_query concatenates it into a valid PIVOT statement.
3. EXECUTE IMMEDIATE runs the dynamic query.

This approach is useful when:

• Departments (or categories) change often.
• You need a generalized procedure or report.

---

5. Comparing CASE vs PIVOT Approaches

Aspect	Using CASE	Using PIVOT
Oracle Version	Works in all	11g and above
Syntax Complexity	Verbose (multiple CASE expressions)	Clean and declarative
Dynamic Columns	Difficult	Easy via Dynamic SQL
Performance	Slightly slower for many CASEs	Usually faster due to optimizer support
Control over Logic	More flexible	Limited to aggregate + pivot

---

6. Example: Transpose Multiple Metrics

You can pivot multiple values simultaneously, such as both average and maximum salary.

SELECT *
FROM (
    SELECT dept, salary FROM emp_salary
)
PIVOT (
    AVG(salary) AS avg_sal,
    MAX(salary) AS max_sal
    FOR dept IN ('HR' AS HR, 'IT' AS IT, 'SALES' AS SALES)
);

Output:

HR_AVG_SAL	HR_MAX_SAL	IT_AVG_SAL	IT_MAX_SAL	SALES_AVG_SAL	SALES_MAX_SAL
62500	65000	77500	80000	70000	70000

---

7. Reverse of Transpose: UNPIVOT

Sometimes you need to convert columns back into rows. Oracle also supports this via UNPIVOT.

SELECT *
FROM (
    SELECT 62500 AS HR, 77500 AS IT, 70000 AS SALES FROM dual
)
UNPIVOT (
    salary FOR dept IN (HR AS 'HR', IT AS 'IT', SALES AS 'SALES')
);

Output:

DEPT	SALARY
HR	62500
IT	77500
SALES	70000

---

8. Performance Tips

1. Use PIVOT when available (Oracle 11g+), as it’s optimized internally.
2. Index the pivoting column (like dept) to speed up aggregations.
3. Avoid using too many CASE expressions, especially if there are hundreds of unique categories.
4. For large datasets, consider materialized views or summary tables.

---

9. Summary

Requirement	Recommended Method
Need compatibility with older Oracle versions	CASE expressions
Using Oracle 11g or later	PIVOT
Need dynamic columns	Dynamic SQL + PIVOT
Need to convert columns back into rows	UNPIVOT