Experts in View

[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