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

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

## Puzzle of the Week #19:

Produce the department salary report (shown below) with the following  assumptions/requirements:

• Use Single SELECT statement only
• DECODE and CASE functions are not allowed
• An employee’s salary is shown in the corresponding department column (10, 20 or 30), all other department columns should contain NULLs.
• The query should work in Oracle 11g.

### Expected Result:

```ENAME              10         20         30
---------- ---------- ---------- ----------
SMITH                        800
ALLEN                                  1600
WARD                                   1250
JONES                       2975
MARTIN                                 1250
BLAKE                                  2850
CLARK            2450
SCOTT                       3000
KING             5000
TURNER                                 1500
JAMES                                   950
FORD                        3000
MILLER           1300```

### #1: Using NULLIF, ABS, and SIGN functions

```SELECT ename, NULLIF(sal * (1-ABS(SIGN(deptno-10))),0) "10",
NULLIF(sal * (1-ABS(SIGN(deptno-20))),0) "20",
NULLIF(sal * (1-ABS(SIGN(deptno-30))),0) "30"
FROM emp
```

### #2: Using NULLIF and INSTR functions

```SELECT ename, NULLIF(sal * INSTR(deptno, 10), 0) "10",
NULLIF(sal * INSTR(deptno, 20), 0) "20",
NULLIF(sal * INSTR(deptno, 30), 0) "30"
FROM emp```

### #3: Using NVL2 and NULLIF functions

```SELECT ename, NVL2(NULLIF(deptno, 10), NULL, 1) * sal "10",
NVL2(NULLIF(deptno, 20), NULL, 1) * sal "20",
NVL2(NULLIF(deptno, 30), NULL, 1) * sal "30"
FROM emp```

### #4: Using PIVOT clause

```SELECT *
FROM  (SELECT deptno, ename, sal
FROM emp)
PIVOT (MAX(sal)
FOR deptno IN (10, 20, 30)
);```

### #5: Using Scalar SELECT statements in SELECT clause

```SELECT ename,
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=10) "10",
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=20) "20",
(SELECT sal FROM emp WHERE empno=e.empno AND deptno=30) "30"
FROM emp e;```

### #6: Using UNION (different sort order)

```SELECT ename, sal "10", NULL "20", NULL "30"
FROM emp
WHERE deptno=10
UNION
SELECT ename, NULL, sal, NULL
FROM emp
WHERE deptno=20
UNION
SELECT ename, NULL, NULL, sal
FROM emp
WHERE deptno=30;

ENAME              10         20         30
---------- ---------- ---------- ----------
ALLEN                                  1600
BLAKE                                  2850
CLARK            2450
FORD                        3000
JAMES                                   950
JONES                       2975
KING             5000
MARTIN                                 1250
MILLER           1300
SCOTT                       3000
SMITH                        800
TURNER                                 1500
WARD                                   125

```

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