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
CodePudding user response:
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>
CodePudding user response:
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'
)
)
);
You still have to pass the list of departments in IN clause of UNPIVOT, So the best possible solution is already provided by Littlefoot.