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