## Puzzle of the Week #10: Fibonacci

With a single SELECT statement calculate first 20 Fibonacci numbers without using Binet’s formula.

Expected Result:

```   N     Fib(n)
---- ----------
1          1
2          1
3          2
4          3
5          5
6          8
7         13
8         21
9         34
10         55
11         89
12        144
13        233
14        377
15        610
16        987
17       1597
18       2584
19       4181
20       6765
```

## Solutions:

#1. Oracle 10g solution (using MODEL clause):

```SELECT n, f AS "Fib(n)"
FROM dual
MODEL
DIMENSION BY (0 d)
MEASURES (0 n, 0 f)
RULES ITERATE(20) (
f[iteration_number]=DECODE(iteration_number, 0,1, 1,1,
f[iteration_number-2]+f[iteration_number-1]),
n[iteration_number]=iteration_number+1
);

N     Fib(n)
--- ----------
1          1
2          1
3          2
4          3
5          5
6          8
7         13
8         21
9         34
10         55
11         89
12        144
13        233
14        377
15        610
16        987
17       1597
18       2584
19       4181
20       6765```

#2. Oracle 11.2g solution (using Recursive WITH clause):

WITH x(n, f1, f2) AS (
SELECT 1, 1, 1
FROM dual
UNION ALL
SELECT n+1, f2, f1+f2
FROM x
WHERE n<20
)
SELECT n, f1 AS “Fib(n)”
FROM x

#3. Oracle 12c solution (using WITH for PL/SQL function):

```WITH
FUNCTION fib(n INTEGER) RETURN NUMBER DETERMINISTIC
AS
BEGIN
RETURN CASE WHEN n IN (1,2) THEN 1
ELSE fib(n-2)+fib(n-1)
END;
END;
SELECT LEVEL n, fib(LEVEL) AS "Fib(n)"
FROM dual
CONNECT BY LEVEL<=20```

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

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

## Puzzle of the Week #9:

All employees are sorted by employee number and need to be split in 3 groups equal in size (as close as possible).  Employees with the smallest numbers will get into the 1st group, the 2nd group will have employees with the next (bigger) range of numbers, etc.

Write a single SELECT statement (against emp table) that would show group number, the range of employee numbers, and the size of each group.

Expected Result:

```Group RANGE           Count
----- ---------- ----------
1 7369-7654           5
2 7698-7844           5
3 7876-7934           4
```

Solutions:

#1: Using NTile Analytic function

```WITH x AS (
SELECT empno, NTILE(3)OVER(ORDER BY empno) nt
FROM emp
)
SELECT nt "Group", MIN(empno)||'-'||MAX(empno) "Range", COUNT(*) "Count"
FROM x
GROUP BY nt
ORDER BY 1;

Group Range           Count
------ ---------- ----------
1 7369-7654           5
2 7698-7844           5
3 7876-7934           4```

#2: Simulating NTile function

```WITH x AS (
SELECT empno, CEIL(ROW_NUMBER()OVER(ORDER BY empno)/CEIL(COUNT(*)OVER()/3)) nt
FROM emp
)
SELECT nt "Group", MIN(empno)||'-'||MAX(empno) "Range", COUNT(*) "Count"
FROM x
GROUP BY nt
ORDER BY 1;

Group Range           Count
------ ---------- ----------
1 7369-7654           5
2 7698-7844           5
3 7876-7934           4```

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 #8:

Find job titles represented in every department. Write a single SELECT statement only.

Expected Result: (Only clerks and managers work in all 3 departments: 10,20, and 30)

```JOB
--------
CLERK
MANAGER
```

### Solutions:

#1: Two COUNT(DISTINCT ..) in HAVING

```SELECT job
FROM emp
GROUP BY job
HAVING COUNT(DISTINCT deptno)=(SELECT COUNT(DISTINCT deptno) FROM emp)
```

#2: Analytic COUNT(DISTINCT ..) with CONNECT BY

```SELECT DISTINCT job
FROM (
SELECT job, deptno, LEVEL level#, COUNT(DISTINCT deptno) OVER() cnt
FROM emp
CONNECT BY job=PRIOR job
AND deptno>PRIOR deptno
)
WHERE level#=cnt
```

#3: Two Analytic COUNT(DISTINCT..)

```WITH x AS (
SELECT deptno, job, COUNT(DISTINCT deptno)OVER() cnt, COUNT(DISTINCT deptno)OVER(PARTITION BY job) cnt2
FROM emp
)
SELECT DISTINCT job
FROM x
WHERE cnt=cnt2
```

OR

```WITH x AS (
SELECT deptno, job, COUNT(DISTINCT deptno)OVER() cnt, COUNT(DISTINCT deptno)OVER(PARTITION BY job) cnt2
FROM emp
)
SELECT job
FROM x
WHERE cnt=cnt2
GROUP BY job
```

#4: Cartesian Product and Two COUNT(DISTINCT ..)

```SELECT a.job
FROM emp a, emp b
GROUP BY a.job
HAVING COUNT(DISTINCT a.deptno)=COUNT(DISTINCT b.deptno)
```

#5: ROLLUP with RANK OVER COUNT(DISTINCT..)

```WITH x AS (
SELECT job, COUNT(DISTINCT deptno) cnt,
RANK()OVER(ORDER BY COUNT(DISTINCT deptno)  DESC) rk
FROM emp
GROUP BY ROLLUP(job)
)
SELECT job
FROM x
WHERE rk=1
AND job IS NOT NULL
```

#6: Analytic COUNT(DITSINCT..) comparison with MINUS

```WITH x AS (
SELECT job,
CASE WHEN COUNT(DISTINCT deptno)OVER()=COUNT(DISTINCT deptno)OVER(PARTITION BY job) THEN 1 END
FROM emp
MINUS
SELECT job, NULL
FROM emp
)
SELECT job
FROM x
```

#7: No COUNT(DISTINCT ..) solution:

```WITH x AS (
SELECT a.deptno, b.job, NVL(COUNT(c.empno),0) idx
FROM (SELECT DISTINCT deptno FROM emp) a CROSS JOIN (SELECT DISTINCT job FROM emp) b
LEFT JOIN emp c ON a.deptno=c.deptno AND b.job=c.job
GROUP BY a.deptno, b.job
)
SELECT job
FROM x
GROUP BY job
HAVING MIN(idx)>0
```

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.

## Two ways to build a salary range report without using CASE function

Interview Question: Produce a salary range report with a single SELECT statement. Decode function is allowed, CASE function – is not.

Level: Intermediate

Expected Result:

```RANGE                             Employees
-------------------------------- ----------
0-999                                     2
1000-2999                                 9
3000-5999                                 3
```

Strategy #1:

```SELECT COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
) AS range,
COUNT(*) "Employees"
FROM emp
GROUP BY COALESCE(DECODE(LEAST(sal, 999), sal, '0-999'),
DECODE(LEAST(sal, 2999), GREATEST(sal, 1000), '1000-2999'),
DECODE(LEAST(sal, 9999), GREATEST(sal, 3000), '3000-5999')
)
ORDER BY 1
```

Explanation:

In Oracle SQL terms, a mathematical condition

`a <=x <=b`

can be  interpreted as

`x BETWEEN a AND b`

however, this condition is good only for CASE function, and not for DECODE. The trick is to use another interpretation:

`LEAST(b,x)=GREATEST(x,a)`

– that can be used in DECODE.

CASE-based Solution:

```SELECT CASE WHEN sal<=999 THEN '0-999'
WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
END AS range,
COUNT(*) "Employees"
FROM emp
GROUP BY CASE WHEN sal<=999 THEN '0-999'
WHEN sal BETWEEN 1000 AND 2999 THEN '1000-2999'
WHEN sal BETWEEN 3000 AND 5999 THEN '3000-5999'
END
ORDER BY 1
```

Strategy #2:

```WITH x AS (
SELECT DECODE(1, (SELECT COUNT(*) FROM dual WHERE emp.sal<=999), '0-999',
(SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 1000 AND 2999), '1000-2999',
(SELECT COUNT(*) FROM dual WHERE emp.sal BETWEEN 3000 AND 5999), '3000-5999'
) AS range
FROM emp
)
SELECT range, COUNT(*) AS "Employees"
FROM x
GROUP BY range
ORDER BY 1
```

Explanation:
This query demonstrates how to mimic CASE function using DECODE and in-line scalar subquery from dual.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

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

## Puzzle of the Week #7

For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.

Expected Result:

```EMPNO ENAME       SUM_ASCII
----- ---------- ----------
7788 SCOTT             397
7566 JONES             383
7499 ALLEN             364
7521 WARD              302
7934 MILLER            453
7902 FORD              299
7369 SMITH             389
7844 TURNER            480
7698 BLAKE             351
7782 CLARK             365
7654 MARTIN            459
7839 KING              297
7900 JAMES             368```

## Solutions:

Solution/Workaround #1: Oracle 12c and up Only (submitted by Zohar Elkayam)

```WITH
FUNCTION sumascii(p_str in varchar2)  RETURN NUMBER
IS
x NUMBER:= 0;
BEGIN
FOR i IN 1..LENGTH(p_str) LOOP
x := x + ASCII(SUBSTR(p_str, i, 1)) ;
END LOOP;
RETURN x;
END;
SELECT empno, ename, sumascii(ename) AS sum_ascii
FROM emp
/
```

Variation of Solution #1 (Recursive function):

```WITH
FUNCTION sumascii(p_str in varchar2)  RETURN NUMBER
IS
BEGIN
IF p_str IS NULL THEN
RETURN 0;
END IF;
RETURN ASCII(p_str) + sumascii(SUBSTR(p_str,2));
END;
SELECT empno, ename, sumascii(ename) AS sum_ascii
FROM emp
/
```

Solution/Workaround #2: Cartesian Product with Generated Numeric Range (by Zohar Elkayam)

```SELECT empno, ename, SUM(ASCII(ename_char)) sum_ascii
FROM (SELECT empno, ename, SUBSTR(ename, i, 1) ename_char
FROM emp, (SELECT LEVEL i
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(LENGTH(ename))                                      FROM emp)                  )       WHERE LENGTH(ename)>=i
)
GROUP BY empno, ename
/
```

Simplified variation of Workaround #2:

```SELECT empno, ename,
SUM(ASCII(SUBSTR(ename, i, 1))) sum_ascii
FROM emp, (SELECT LEVEL i
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(LENGTH(ename))                                FROM emp)            )  WHERE LENGTH(ename)>=i
GROUP BY empno, ename
/
```

Solution/Workaround #3: In-Line Scalar Subquery

```SELECT empno, ename,
(SELECT SUM(ASCII(SUBSTR(a.ename, LEVEL, 1)))
FROM dual
CONNECT BY LEVEL<=LENGTH(a.ename)) AS sum_ascii
FROM emp a
/
```

Solution #4/Workaround : Recursive WITH clause

```WITH x(n, empno, ename, letter) AS (
SELECT 1 AS n, empno, ename, SUBSTR(ename, 1, 1)
FROM emp
UNION ALL
SELECT x.n+1, empno, ename, SUBSTR(ename, n+1, 1)
FROM x
WHERE LENGTH(ename)>=n+1
)
SELECT empno, ename, SUM(ASCII(letter)) sum_ascii
FROM x
GROUP BY empno, ename
/
```

Solution/Workaround #5: Use DUMP function and Regular Expressions (submitted by Sunitha)

```SELECT empno, ename, SUM(REGEXP_SUBSTR(nm, '\d+', 1, occ)) AS sum_ascii
FROM (SELECT empno, ename, REGEXP_REPLACE(DUMP(ename), '.*: (\d.*)\$', '\1') nm
FROM emp),
(SELECT LEVEL occ FROM dual CONNECT BY LEVEL <=ANY(SELECT LENGTH(ename) FROM emp))
GROUP BY empno, ename
/

```

Solution/Workaround #6: Use LATERAL View (Oracle 12c and up)

```SELECT empno, ename, sum_ascii
FROM emp e, LATERAL (SELECT SUM(ASCII(SUBSTR(e.ename,LEVEL,1)) ) sum_ascii
FROM dual
CONNECT BY LEVEL<=LENGTH(e.ename) ) x
```

Solution/Workaround #7: Use TABLE/CAST/MULTISET function composition

```SELECT empno, ename, x.column_value AS sum_ascii
FROM emp e,
TABLE(CAST(MULTISET(SELECT SUM(ASCII(SUBSTR(e.ename,LEVEL,1)) ) sum_ascii
FROM dual
CONNECT BY LEVEL<=LENGTH(e.ename)
) AS sys.odcinumberlist
)
) x
```

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

# Puzzle of the Week #7

For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.

Expected Result:

```EMPNO ENAME       SUM_ASCII
----- ---------- ----------
7788 SCOTT             397
7566 JONES             383
7499 ALLEN             364
7521 WARD              302
7934 MILLER            453
7902 FORD              299
7369 SMITH             389
7844 TURNER            480
7698 BLAKE             351
7782 CLARK             365
7654 MARTIN            459
7839 KING              297
7900 JAMES             368```

A correct answer (and workarounds!) will be published here in a week.

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

Find all employees who is paid one of top 4 salaries in the entire company without using sub-queries and in-line views/WITH clause.

Expected Result:

```ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850
```

Method/Workaround #1: Use Aggregation over Cartesian Product

This method works in all RDBMS systems, such as Oracle, SQL Server, Sybase, Teradata, etc.

```SELECT a.ename, a.sal
FROM emp a, emp b
WHERE a.sal<=b.sal
GROUP BY a.ename, a.sal
HAVING COUNT(DISTINCT b.sal)<=4
ORDER BY 2 DESC;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850
```

This method is described in details in my book Oracle SQL Tricks and Workarounds.

Method/Workaround #2: Use DENSE_RANK and MINUS

This method is more Oracle specific, but it is as good as the 1st one. It was suggested by one of the contest participants:

```SELECT ename,
CASE WHEN DENSE_RANK()OVER(ORDER BY sal DESC)<=4 THEN sal END sal
FROM emp
MINUS
SELECT ename, NULL
FROM emp
ORDER BY 2 DESC;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850
```

This is a very elegant variation of the following query with MINUS substituting the filter for analitic function result:

```SELECT ename, sal
FROM (SELECT ename, sal, DENSE_RANK()OVER(ORDER BY sal DESC) rk
FROM emp)
WHERE rk<=4;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850
```

Interestingly enough, Teradata SQL has a special clause QUALIFY for that matter. It is very elegant and maybe one day it will become an ANSII standard:

```SELECT ename, sal
FROM emp
QUALIFY DENSE_RANK()OVER(ORDER BY sal DESC)<=4
```

Finally, I would like to share a couple of more traditional approaches that DO USE subqueries:

```SELECT ename, sal
FROM emp a
WHERE 4>=(SELECT COUNT(DISTINCT b.sal)
FROM emp b
WHERE b.sal>=a.sal)
ORDER BY sal DESC;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850
```
```SELECT ename, sal
FROM emp a
WHERE 4>(SELECT COUNT(DISTINCT b.sal)
FROM emp b
WHERE b.sal>a.sal)
ORDER BY sal DESC;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850
```

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.

## Round 1 Playoff Schedule

N teams (N between 1 and 32) just finished the season and are all qualified for the playoff. If the number of teams were 2, 4, 8, 16, or 32 (powers of 2), the playoff schedule would be trivial: 1st team plays vs last team, 2nd – vs 2nd from the last, etc. However, there is no guarantee that the number of teams would be a power of 2. The challenge is to write a single SELECT statement that accepts the number of teams as a parameter and generates the round 1 pairings.

There should be 1, 2, 4, 8, or 16 teams (power of 2) in the 2nd round.

Expected Results:

For 6 teams:

```Game # Playoff Round 1 Pairings
------ ---------------------------
1 Team-3 vs Team-6
2 Team-4 vs Team-5
```

For 7 teams:

```Game # Playoff Round 1 Pairings
------ --------------------------
1 Team-2 vs Team-7
2 Team-3 vs Team-6
3 Team-4 vs Team-5
```

For 8 teams:

```Game # Playoff Round 1 Pairings
------ ------------------------
1 Team-1 vs Team-8
2 Team-2 vs Team-7
3 Team-3 vs Team-6
4 Team-4 vs Team-5
```

For 1 team (no playoffs needed):

```Game # Playoff Round 1 Pairings
------ ---------------------------
Team-1 is a Champion!
```

For 11 teams:

```Game # Playoff Round 1 Pairings
------ ---------------------------
1 Team-6 vs Team-11
2 Team-7 vs Team-10
3 Team-8 vs Team-9
```

You can use a substitution variable and run the query in SQL*Plus to test the results.

A correct answer (and workarounds!) will be published here in a week.

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 #3 Solutions

Puzzle of the week #3Calendar Summary Report:

Write a single SELECT statement that outputs number of Sundays, Mondays, Tuesdays, etc in each month of the current year.

The output should look like this:

```MONTH  SUN  MON  TUE  WED  THU  FRI  SAT
----- ---- ---- ---- ---- ---- ---- ----
JAN      5    4    4    4    4    5    5
FEB      4    5    4    4    4    4    4
MAR      4    4    5    5    5    4    4
APR      4    4    4    4    4    5    5
MAY      5    5    5    4    4    4    4
JUN      4    4    4    5    5    4    4
JUL      5    4    4    4    4    5    5
AUG      4    5    5    5    4    4    4
SEP      4    4    4    4    5    5    4
OCT      5    5    4    4    4    4    5
NOV      4    4    5    5    4    4    4
DEC      4    4    4    4    5    5    5
```

We suggest you to go over the post that explains how to generate various date ranges before checking the solutions below.

Solution #1: Using PIVOT simulation

```WITH days AS (
SELECT TRUNC(SYSDATE,'YEAR')+ROWNUM-1 d
FROM dual
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
SELECT TO_CHAR(d,'MON') Month,
SUM(CASE WHEN TO_CHAR(d,'DY')='SUN' THEN 1 END) SUN,
SUM(CASE WHEN TO_CHAR(d,'DY')='MON' THEN 1 END) MON,
SUM(CASE WHEN TO_CHAR(d,'DY')='TUE' THEN 1 END) TUE,
SUM(CASE WHEN TO_CHAR(d,'DY')='WED' THEN 1 END) WED,
SUM(CASE WHEN TO_CHAR(d,'DY')='THU' THEN 1 END) THU,
SUM(CASE WHEN TO_CHAR(d,'DY')='FRI' THEN 1 END) FRI,
SUM(CASE WHEN TO_CHAR(d,'DY')='SAT' THEN 1 END) SAT
FROM days
GROUP BY TO_CHAR(d,'MON'), TO_CHAR(d,'MM')
ORDER BY TO_CHAR(d,'MM');
```

Solution #2: Using PIVOT

```SELECT month, mon, sun, mon, tue, wed, thu, fri, sat
FROM
(
SELECT TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'MON') month,
TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'DY') dy,
TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'MM') mm
FROM dual
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'YEAR')+ROWNUM-1, 'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
PIVOT
(
COUNT(dy)
FOR dy IN ('SUN' sun, 'MON' mon, 'TUE' tue, 'WED' wed, 'THU' thu, 'FRI' fri, 'SAT' sat)
)
ORDER BY mm;
```

Solution #3: Using PIVOT simulation and Recursive WITH

```WITH days(d) AS
(
SELECT TRUNC(SYSDATE,'YEAR') d
FROM dual
UNION ALL
SELECT d+1
FROM days
WHERE TO_CHAR(d+1,'YYYY')=TO_CHAR(SYSDATE,'YYYY')
)
SELECT TO_CHAR(d,'MON') Month,
SUM(CASE WHEN TO_CHAR(d,'DY')='SUN' THEN 1 END) SUN,
SUM(CASE WHEN TO_CHAR(d,'DY')='MON' THEN 1 END) MON,
SUM(CASE WHEN TO_CHAR(d,'DY')='TUE' THEN 1 END) TUE,
SUM(CASE WHEN TO_CHAR(d,'DY')='WED' THEN 1 END) WED,
SUM(CASE WHEN TO_CHAR(d,'DY')='THU' THEN 1 END) THU,
SUM(CASE WHEN TO_CHAR(d,'DY')='FRI' THEN 1 END) FRI,
SUM(CASE WHEN TO_CHAR(d,'DY')='SAT' THEN 1 END) SAT
FROM days
GROUP BY TO_CHAR(d,'MON'), TO_CHAR(d,'MM')
ORDER BY TO_CHAR(d,'MM');
```

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.

## Generate a department/employee roll report

Puzzle: Generate a department /employee roll report (with a single  SELECT statement) that would look as following:

```10         20         30
---------- ---------- -------
KING       FORD       BLAKE
MILLER     JONES      JAMES
SCOTT      MARTIN
SMITH      TURNER
WARD
```

Assumption: Only departments 10, 20, and 30 are expected in the output.

Note that columns in the report may and will likely contain different number of values. This makes the puzzle somewhat tricky.

Method/Workaround #1: Using FULL join on 3 in-line views

```WITH d10 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=10
),   d20 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=20
),   d30 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=30
)
SELECT  d10.ename "10", d20.ename "20", d30.ename "30"
FROM d10 FULL JOIN d20 ON d10.rn=d20.rn
FULL JOIN d30 ON d10.rn=d30.rn OR d20.rn=d30.rn
ORDER BY COALESCE(d10.rn, d20.rn, d30.rn)
```

Note the OR operator in the 2nd FULL JOIN condition. If you omit it, the result will be different:

```WITH d10 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=10
),   d20 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=20
),   d30 AS (
SELECT ename, ROW_NUMBER()OVER(ORDER BY ename) rn
FROM emp
WHERE deptno=30
)
SELECT  d10.ename "10", d20.ename "20", d30.ename "30"
FROM d10 FULL JOIN d20 ON d10.rn=d20.rn
FULL JOIN d30 ON d10.rn=d30.rn --OR d20.rn=d30.rn
ORDER BY COALESCE(d10.rn, d20.rn, d30.rn)
/

10         20         30
---------- ---------- -------
KING       FORD       BLAKE
MILLER     JONES      JAMES
MARTIN
SCOTT
TURNER
SMITH
WARD
```

Since we don’t know which department will have more employees, we can’t reliably pick the right order for joining tables, so we have to twist it with an additional OR condition.

Overall, this solution is quite simple and straightforward, but very bulky and not scallable. Imagine having 10 departments to show in the report. Not a very neat SQL.
The following 2 workarounds offer substantially better solution.

Method/Workaround #2: Using PIVOT clause

```SELECT "10","20","30"
FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn, deptno, ename
FROM emp
)
PIVOT
(
MAX(ename)
FOR deptno IN (10,20,30)
)
ORDER BY rn
```

Note, that aggregation is done by the “rn” column which is the only common attribute in all 3 columns. Since rn is unique in each deparment, grouping by it will make MAX(ename) evaluate to ename itself as each group will always have 1 value.

Method/Workaround #3: Traditional simulation of PIVOT clause

```WITH x AS (
SELECT CASE WHEN deptno=10 THEN ename END "10",
CASE WHEN deptno=20 THEN ename END "20",
CASE WHEN deptno=30 THEN ename END "30",
ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) rn
FROM emp
)
SELECT MAX("10") AS "10",
MAX("20") AS "20",
MAX("30") AS "30"
FROM x
GROUP BY rn
ORDER BY rn
```

It is a less compact but much more generic approach in a sense that it will work even in those RDBMS that don’t support PIVOT. The idea behind this method is identical to the one used in Method 2.

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.