7 Solutions to 2018 Oracle SQL Puzzle of the Week #6

Conference Team Puzzle

Research department from Dallas (#20) needs to delegate a team of 3 to a annual conference. Create a list of all possible teams of 3 employees from that department.

  • Use a single SELECT statement only
  • Use scott.emp table
  • Exactly 3 employees (no repetitions) must be presented for each team

Solutions:

Essentially, all 6 solutions represent 6 different ways how you can UNPIVOT a result set. Some of those are fairly standard and well known (#3, #4, and #7) while the others are quite tricky (#2, #5, and #6).

Solution #1: Using UNPIVOT clause:

WITH t AS (  --#1: Using UNPIVOT 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, team_member 
FROM x 
UNPIVOT (team_member FOR dummy IN (e1,e2,e3) ) 
ORDER BY 1,2

Solution #2: Mimicking UNPIVOT with IN operator

WITH t AS (  --#2: Mimicking UNPIVOT with IN operator 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT x.team_no, t.ename team_member 
FROM t JOIN x ON t.ename IN (x.e1, x.e2, x.e3) 
ORDER BY 1,2

Solution #3: Mimicking UNPIVOT with MODEL clause

WITH t AS ( --#3: Mimicking UNPIVOT with MODEL clause 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, team_member 
FROM x 
MODEL 
PARTITION BY (team_no) 
DIMENSION BY (1 AS dummy) 
MEASURES (e1 AS team_member, e2, e3) 
RULES( 
    team_member[2]=e2[1], 
    team_member[3]=e3[1] 
) 
ORDER BY 1,2

Solution #4: Mimicking UNPIVOT with UNION operators

WITH t AS ( --#4: Mimicking UNPIVOT with UNIONs 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, e1 team_member 
FROM x 
UNION  
SELECT team_no, e2 team_member 
FROM x 
UNION  
SELECT team_no, e3 team_member 
FROM x

Solution #5: Mimicking UNPIVOT with DECODE and Cartesian Product

WITH t AS ( --#5: Mimicking UNPIVOT with DECODE and Cartesian Product 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, DECODE(y.rn, 1, e1, 2, e2, 3, e3) team_member 
FROM x, (SELECT ROWNUM rn FROM dual CONNECT BY LEVEL<=3) y 
ORDER BY 1,2

Solution #6: Mimicking UNPIVOT with COALESCE and GROUPING SETS

WITH t AS ( --#6: Mimicking UNPIVOT with COALESCE and GROUPING SETS 
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
          JOIN t t3 ON t2.empno>t3.empno 
) 
SELECT team_no, COALESCE(e1, e2, e3) team_member 
FROM x 
GROUP BY team_no, GROUPING SETS(e1,e2,e3) 
ORDER BY 1,2

Solution #7: Mimicking UNPIVOT with Recursive CTE

WITH t AS ( --#7: Mimicking UNPIVOT with Recursive CTE
SELECT ename, empno 
FROM scott.emp 
WHERE deptno=20 
), x AS ( 
SELECT ROWNUM team_no, t1.ename e1, t2.ename e2, t3.ename e3 
FROM t t1 JOIN t t2 ON t1.empno>t2.empno 
 JOIN t t3 ON t2.empno>t3.empno 
), y(team_no, team_member, e2, e3, lvl) AS (
SELECT team_no, e1, e2, e3, 1
FROM x
UNION ALL
SELECT team_no, DECODE(lvl+1, 2, e2, e3), e2, e3, lvl+1
FROM y
WHERE lvl+1<=3
)
SELECT team_no, team_member
FROM y
ORDER BY 1,2

You can execute the above SQL statements in Oracle Live SQL environment.
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.

 

Advertisements