CODEWITHSUNDEEP
sqloracle-database
Martin James
Martin James

Reputation: 146

Transposing columns to rows in the same table

I have the following table :

EmployeeId Dept1 Dept2 Dept3
150        10    55    6

this is the query to get that result :

SELECT  EmployeeId, Dept1, Dept2, Dept3 FROM Employee_History

This is my expected output :

EmployeeId Dept
150        10
150        55
150        6

Upvotes: 0

Views: 83

Answers (2)

Ankit Bajpai
Ankit Bajpai

Reputation: 13527

There is a clause in Oracle as UNPIVOT which allows you to transpose the rows dynamically -

WITH DATA AS (SELECT 150 EmployeeId, 10 Dept1, 55 Dept2, 6 Dept3 FROM DUAL)
SELECT EmployeeId, DEPT
  FROM (SELECT * FROM DATA
        UNPIVOT (DEPT FOR NAMES IN (Dept1 AS 'Dept1',
                                    Dept2 AS 'Dept2',
                                    Dept3 AS 'Dept3'
                                   )
                )
       );

Demo.

You still have to pass the list of departments in IN clause of UNPIVOT, So the best possible solution is already provided by Littlefoot.

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143103

UNION 3 dept columns:

SQL> select employee_id, dept1 as dept from employee_history
  2  union all
  3  select employee_id, dept2 as dept from employee_history
  4  union all
  5  select employee_id, dept3 as dept from employee_history;

EMPLOYEE_ID       DEPT
----------- ----------
        150         10
        150         55
        150          6

SQL>

Upvotes: 1

Related Questions