Puzzle of the Week #21:
Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:
- Use Single SELECT statement only
- Use mgr column to identify employee’s immediate manager
- The query should work in Oracle 11g.
- A preferred solution should use only a single instance of emp table.
Expected Result:
NAME1 NAME2 NAME3 ---------- ---------- ------ SMITH FORD JONES ALLEN BLAKE KING WARD BLAKE KING JONES KING MARTIN BLAKE KING BLAKE KING CLARK KING SCOTT JONES KING KING TURNER BLAKE KING ADAMS SCOTT JONES JAMES BLAKE KING FORD JONES KING MILLER CLARK KING
Solutions:
#1. Using connect_by_root, sys_connect_by_path, and regexp_substr functions
col name1 for a10
col name2 for a10
col name3 for a10
WITH x AS(
SELECT CONNECT_BY_ROOT(ename) name,
SYS_CONNECT_BY_PATH(ename, ',') path,
CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT name, REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 2) name2,
REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 3) name3
FROM x
GROUP BY name, empno
ORDER BY empno;
#2. Using CONNECT BY twice
WITH x AS ( SELECT ename, PRIOR ename mname, empno, mgr FROM emp WHERE LEVEL=2 OR mgr IS NULL CONNECT BY PRIOR empno=mgr ) SELECT ename name1, mname name2, MAX(PRIOR mname) name3 FROM x WHERE LEVEL<=2 CONNECT BY PRIOR empno=mgr GROUP BY ename, mname, empno ORDER BY empno
#3. Using CONNECT BY and Self Outer Join
WITH x AS ( SELECT ename, PRIOR ename mname, PRIOR mgr AS mgr, empno FROM emp WHERE LEVEL=2 OR mgr IS NULL CONNECT BY PRIOR empno=mgr ) SELECT x.ename name1, x.mname name2, e.ename name3 FROM x LEFT JOIN emp e ON x.mgr=e.empno ORDER BY x.empno
#4. Using 2 Self Outer Joins
SELECT a.ename name1, b.ename name2, c.ename name3 FROM emp a LEFT JOIN emp b ON a.mgr=b.empno LEFT JOIN emp c ON b.mgr=c.empno ORDER BY a.empno
#5. Using CONNECT BY and PIVOT
SELECT name1, name2, name3 FROM ( SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno FROM emp WHERE LEVEL<=3 CONNECT BY empno=PRIOR mgr ) PIVOT( MAX(ename) FOR lvl IN (1 AS name1, 2 AS name2, 3 AS name3) ) ORDER BY empno;
#6. PIVOT Simulation
WITH x AS ( SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno FROM emp WHERE LEVEL<=3 CONNECT BY empno=PRIOR mgr ) SELECT MAX(DECODE(lvl, 1, ename)) name1, MAX(DECODE(lvl, 2, ename)) name2, MAX(DECODE(lvl, 3, ename)) name3 FROM x GROUP BY empno ORDER BY empno;
#7. Using CONNECT BY and no WITH/Subqueries (Credit to Krishna Jamal)
SELECT ename Name1, PRIOR ename Name2, DECODE(LEVEL, 3, CONNECT_BY_ROOT(ename), 4, TRIM(BOTH ' ' FROM REPLACE( REPLACE(SYS_CONNECT_BY_PATH(PRIOR ename, ' '), PRIOR ename), CONNECT_BY_ROOT(ename))) ) Name3 FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ORDER BY empno;
#8. A composition of Methods 1 and 7:
SELECT ename Name1, PRIOR ename Name2, CASE WHEN LEVEL IN (3,4) THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ename, ','),'[^,]+',1,LEVEL-2) END AS Name3 FROM emp START WITH mgr IS NULL CONNECT BY PRIOR empno = mgr ORDER BY empno;
#9. Using NTH_VALUE Analytic function (Oracle 11.2 and up):
WITH x AS ( SELECT CONNECT_BY_ROOT(ename) n1, CONNECT_BY_ROOT(empno) empno, NTH_VALUE(ename, 2) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n2, NTH_VALUE(ename, 3) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n3 FROM emp WHERE LEVEL<=3 CONNECT BY empno=PRIOR mgr ) SELECT n1 name1, MAX(n2) name2, MAX(n3) name3 FROM x GROUP BY n1, empno ORDER BY empno
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.
sqlpatterns 