Oracle Pivot and Unpivot queries

 Oracle Pivot and Unpivot queries

In Oracle, you can convert columns to rows using various techniques such as SQL pivot queries, the UNPIVOT operator, or the CONNECT BY clause with hierarchical queries. Here are examples of each method:

1. Using SQL Pivot Query:

SELECT * FROM (

  SELECT emp_id, emp_name, salary
  FROM employees
)
PIVOT (
  MAX(salary)
  FOR (emp_name) IN ('John' AS John_Salary, 'Alice' AS Alice_Salary, 'Bob' AS Bob_Salary)
);

In this example, `employees` is the table containing the data. We're selecting the columns `emp_id`, `emp_name`, and `salary` from this table and then pivoting the `emp_name` column values into separate columns.

2. Using UNPIVOT Operator:

SELECT emp_id, emp_name, salary
FROM (
  SELECT emp_id, John_Salary, Alice_Salary, Bob_Salary
  FROM (
    SELECT emp_id, emp_name, salary
    FROM employees
  )
  UNPIVOT (
    salary FOR emp_name IN (John_Salary, Alice_Salary, Bob_Salary)
  )
);

In this example, we're using the UNPIVOT operator to convert multiple columns into rows.

3. Using CONNECT BY Clause:

SELECT emp_id, emp_name, salary
FROM (
  SELECT emp_id, emp_name, salary,
         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY emp_name) AS rn
  FROM employees
)
CONNECT BY PRIOR emp_id = emp_id AND PRIOR rn = rn - 1;

This approach uses hierarchical queries to convert multiple columns into rows.

Choose the method that best fits your specific requirements and database schema. Each method has its advantages and limitations, so consider factors such as performance, readability, and maintenance when making your decision.