# 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.