-
Experts in View
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 !
-
what have you come up with so far when trying it for yourself?
-
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
Last edited by gamyers; 06-07-2005 at 06:36 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|