Hi everyone,
I need to do this query,
Suppose we have 2 departments with n employees on each department
I need to select departments with just 2 employees in the same row, here example
| Department | Department Name | Employee1 | Employee2 |
|---|
| 10 | HR | Marc | Steve |
| 20 | IT | Paul | |
| 30 | New Department | | |
| 40 | Finance | Jean | Michael |
This query below didn't work because it displays only the first employee but I need to display 2 employees (same as the table above)
select
deptno,
(select ename from emp, dept where rownum =1 and emp.deptno = dept.deptno) employee1,
(select ename from emp, dept where rownum = 2 and emp.deptno = dept.deptno) employee2
from dept;
...
I did this but with function returning the Emp1Dep1 Emp2Dep1
function get_ename(p_dept number,p_col number)
return varchar2
is
v_ename varchar2(4000);
v_first boolean := true;
BEGIN
select ename
into v_ename
from
(
select ename, rownum as rn
from (
select ename
from emp ,dept
where emp.deptno = p_dept
order by 1 asc
)
)
where rn = p_col;
return v_ename;
END;
and just put
select
get_ename(deptno ,1 ) employee1,
get_ename(deptno ,2 ) employee2
from dept;
Is there a best way to do this?
NB: 11g database
Thanks