Experts in View

[Eric_2005]

I am trying to create a View with 2 oracle table.

Employee
=======
EmpID
Empname

EmployeeDetails
=============
EmpDetailID
EmpID
DeptID
FromDate
ToDate

I have to show on screen, like below :-

EmpID EmpName Dept1(From - To) Dept2(From - To) Dept3(From - To)
===================================================================

In "EmployeeDetails" table, empid can be multiple with unique deptid .
say,

empid =1
deptid =1
fromdate=1/1/2005
todate=2/1/2005

empid =1
deptid =2
fromdate=3/23/2005
todate=3/29/2005

empid =1
deptid =4
fromdate=1/2/2005
todate=3/18/2005

empid =8
deptid =2
fromdate=1/5/2005
todate=5/11/2005


So, how can we create view , so that shows the output I shown above ?

Thanks !

[davey23uk]

what have you come up with so far when trying it for yourself?

[gamyers]

CREATE TABLE a
(EmpID NUMBER,
DeptID NUMBER,
FromDate DATE,
ToDate DATE)

BEGIN
INSERT INTO a VALUES (1,1,'01/jan/1980','30/jan/1980');
INSERT INTO a VALUES (1,5,'01/jan/1981','30/jan/1981');
INSERT INTO a VALUES (1,3,'01/jan/1982','30/jan/1982');
INSERT INTO a VALUES (2,3,'01/jan/1990','30/jan/1990');
INSERT INTO a VALUES (2,1,'01/jan/1991','30/jan/1991');
INSERT INTO a VALUES (2,5,'01/jan/1992','30/jan/1992');
INSERT INTO a VALUES (3,8,'01/jan/1985','30/jan/1985');
INSERT INTO a VALUES (3,9,'01/jan/1986','30/jan/1986');
END;

SELECT empid,
MAX(deptid1), MAX(fromdate1), MAX(todate1),
MAX(deptid2), MAX(fromdate2), MAX(todate2),
MAX(deptid3), MAX(fromdate3), MAX(todate3)
FROM(
SELECT empid,
CASE WHEN rnk = 1 THEN deptid END deptid1,
CASE WHEN rnk = 1 THEN fromdate END fromdate1,
CASE WHEN rnk = 1 THEN todate END todate1,
CASE WHEN rnk = 2 THEN deptid END deptid2,
CASE WHEN rnk = 2 THEN fromdate END fromdate2,
CASE WHEN rnk = 2 THEN todate END todate2,
CASE WHEN rnk = 3 THEN deptid END deptid3,
CASE WHEN rnk = 3 THEN fromdate END fromdate3,
CASE WHEN rnk = 3 THEN todate END todate3
FROM
(SELECT rank() over (PARTITION BY empid ORDER BY fromdate) rnk, a.* FROM a))
GROUP BY empid