## Puzzle of the Week #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

• Use Single SELECT statement only
• Use mgr column to identify employee’s immediate manager
• The query should work in Oracle 11g.
• A preferred solution should use only a single instance of emp table.

### Expected Result:

```NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING```

### Solutions:

#1. Using connect_by_root, sys_connect_by_path, and regexp_substr functions

```col name1 for a10
col name2 for a10
col name3 for a10
WITH x AS(
SELECT CONNECT_BY_ROOT(ename) name,
SYS_CONNECT_BY_PATH(ename, ',') path,
CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT name, REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 2) name2,
REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 3) name3
FROM x
GROUP BY name, empno
ORDER BY empno;```

#2. Using CONNECT BY twice

```WITH x AS (
SELECT ename, PRIOR ename mname, empno, mgr
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT ename name1, mname name2, MAX(PRIOR mname) name3
FROM x
WHERE LEVEL<=2
CONNECT BY PRIOR empno=mgr
GROUP BY ename, mname, empno
ORDER BY empno```

#3. Using CONNECT BY and Self Outer Join

```WITH x AS (
SELECT ename, PRIOR ename mname, PRIOR mgr AS mgr, empno
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT x.ename name1, x.mname name2, e.ename name3
FROM x LEFT JOIN emp e ON x.mgr=e.empno
ORDER BY x.empno```

#4. Using 2 Self Outer Joins

```SELECT a.ename name1, b.ename name2, c.ename name3
FROM emp a LEFT JOIN emp b ON a.mgr=b.empno
LEFT JOIN emp c ON b.mgr=c.empno
ORDER BY a.empno```

#5. Using CONNECT BY and PIVOT

```SELECT name1, name2, name3
FROM (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
PIVOT(
MAX(ename)
FOR lvl IN (1 AS name1, 2 AS name2, 3 AS name3)
)
ORDER BY empno;```

#6. PIVOT Simulation

```WITH x AS (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT MAX(DECODE(lvl, 1, ename)) name1,
MAX(DECODE(lvl, 2, ename)) name2,
MAX(DECODE(lvl, 3, ename)) name3
FROM x
GROUP BY empno
ORDER BY empno;```

#7. Using CONNECT BY and no WITH/Subqueries (Credit to Krishna Jamal)

```SELECT ename Name1, PRIOR ename Name2,
DECODE(LEVEL,
3, CONNECT_BY_ROOT(ename),
4, TRIM(BOTH ' ' FROM
REPLACE(
REPLACE(SYS_CONNECT_BY_PATH(PRIOR ename, ' '), PRIOR ename),
CONNECT_BY_ROOT(ename)))
) Name3
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER BY ROWID;```

#8. A composition of Methods 1 and 7:

```SELECT ename Name1, PRIOR ename Name2,
CASE WHEN LEVEL IN (3,4)
THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ename, ','),'[^,]+',1,LEVEL-2)
END AS Name3
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER BY ROWID;```

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

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

## Puzzle of the Week #17:

Write a single SELECT statement that would show the years of hire in each department. The result should have 3 columns (see below): deptno, year1, and year2. If a department only hired during 1 calendar year, this year should be shown in year1 column (see deptno 30) and year2 column should be blank. If a department hired during 2 calendar years, the first year should be should be shown in year1 column, and the 2nd year should be shown in year2 column (see deptno 10). In all other cases, show 1st year in year1 column and “More (N)” where N is the number of years that department did the hiring (see deptno 20).

### Expected Result:

```DEPTNO Year 1   Year 2
------ -------- --------
10 1981     1982
20 1980     More (3)
30 1981

```

## Solutions

### #1: Using COUNT(DISTINCT ..)

```SELECT deptno, MIN(EXTRACT(YEAR FROM hiredate)) AS "Year 1",
CASE COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))
WHEN 1 THEN ''
WHEN 2 THEN TO_CHAR(MAX(EXTRACT(YEAR FROM hiredate)))
ELSE 'More (' || COUNT(DISTINCT EXTRACT(YEAR FROM hiredate)) || ')'
END AS "Year 2"
FROM emp
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

### #2: Using DENSE_RANK Analytic Function

```WITH x AS (
SELECT deptno, EXTRACT(YEAR FROM hiredate) hire_year,
DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate)) rk
FROM emp
)
SELECT deptno, MIN(hire_year) "Year 1",
CASE MAX(rk) WHEN 1 THEN ''
WHEN 2 THEN CAST(MAX(hire_year) AS CHAR(4))
ELSE 'More (' || MAX(rk) || ')'
END AS "Year 2"
FROM x
GROUP BY deptno
ORDER BY 1;

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

### #3: Using PIVOT clause

```SELECT deptno, Y1 "Year 1",
CASE WHEN cnt>2 THEN 'More (' || cnt || ')'
ELSE TO_CHAR(Y2)
END "Year 2"
FROM
(
SELECT deptno, EXTRACT(YEAR FROM hiredate) yr,
CASE DENSE_RANK()OVER(PARTITION BY deptno ORDER BY EXTRACT(YEAR FROM hiredate))
WHEN 1 THEN 'Y1'
WHEN 2 THEN 'Y2'
END AS Y,
COUNT(DISTINCT EXTRACT(YEAR FROM hiredate))OVER(PARTITION BY deptno) cnt
FROM emp
)
PIVOT
(
MAX(yr)
FOR y IN ('Y1' y1,'Y2' y2)
);

DEPTNO     Year 1 Year 2
------ ---------- --------
10       1981 1982
20       1980 More (3)
30       1981```

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

## Build Department Size Bar Chart Report with a Single SELECT statement.

Recently, when I was working on a completely different problem, I realized that in some cases, SQL plus will allow us generating simple bar graphs on selected data. I challenged myself to build a “graph” that will look like this:

```10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
X  X
X  X
X
```

Each column represents a “bar” and its “height” represents the number of employees working in a corresponding department.

Below, you will find several strategies for building such “graphs” as well as generating “reverse graphs”:

```  N 10 20 30
--- -- -- --
6       X
5    X  X
4    X  X
3 X  X  X
2 X  X  X
1 X  X  X
```

Method/Workaround 1: Pivot simulation

```WITH x AS (SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
), y AS (
SELECT LEVEL n, 'X' as c
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
SELECT MAX(CASE WHEN x.deptno=10 THEN y.c END) "10",
MAX(CASE WHEN x.deptno=20 THEN y.c END) "20",
MAX(CASE WHEN x.deptno=30 THEN y.c END) "30"
FROM y JOIN x ON y.n<=x.cnt
GROUP BY y.n
ORDER BY y.n
/

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
X  X
X  X
X

```

Method/Workaround 2: Pivot

```SELECT "10","20","30"
FROM (
WITH x AS (
SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
)
SELECT LEVEL n, deptno, 'X' as c
FROM dual, x
WHERE LEVEL<=x.cnt
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
PIVOT (
MAX(c)
FOR deptno IN (10 "10",20 "20",30 "30")
)
ORDER BY N
/
```

Method/Workaround 3: Leverage Department/Employee Roll Puzzle:

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

10 20 30
-- -- --
X  X  X
X  X  X
X  X  X
X  X
X  X
X

```

Method/Workaround 4:

```WITH x AS (
SELECT CASE WHEN deptno=10 THEN 'X' END "10",
CASE WHEN deptno=20 THEN 'X' END "20",
CASE WHEN deptno=30 THEN 'X' 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;
```

### Reverse Bar Chart Report:

Method/Workaround 1:

```WITH x AS (SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
), y AS (
SELECT LEVEL n, 'X' as c
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
SELECT n,
MAX(CASE WHEN x.deptno=10 THEN y.c END) "10",
MAX(CASE WHEN x.deptno=20 THEN y.c END) "20",
MAX(CASE WHEN x.deptno=30 THEN y.c END) "30"
FROM y JOIN x ON y.n<=x.cnt
GROUP BY y.n
ORDER BY y.n DESC;

N 10 20 30
--- -- -- --
6       X
5    X  X
4    X  X
3 X  X  X
2 X  X  X
1 X  X  X

```

Method/Workaround 2:

```SELECT *
FROM (
WITH x AS (
SELECT deptno, COUNT(*) cnt
FROM emp
GROUP BY deptno
)
SELECT LEVEL n, deptno, 'X' as c
FROM dual, x
WHERE LEVEL<=x.cnt
CONNECT BY LEVEL<=(SELECT MAX(cnt) FROM x)
)
PIVOT (
MAX(c)
FOR deptno IN (10 "10",20 "20",30 "30")
)
ORDER BY N DESC;
```

Method/Workaround 3: Leverage Department/Employee Roll Puzzle:

```SELECT *
FROM (
SELECT ROW_NUMBER()OVER(PARTITION BY deptno ORDER BY ename) n,
deptno, 'X' c
FROM emp
)
PIVOT
(
MAX(c)
FOR deptno IN (10,20,30)
)
ORDER BY n DESC;

```

Method/Workaround 4:

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

```

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.

## Puzzle of the Week Challenge – Solutions to the 2nd Puzzle

Last week we presented the 2nd puzzle of our contest, Puzzle of the Week. Today we publish correct answers for that puzzle:
Thanks to all who accepted the challenge!

Dish washing schedule puzzle:

 Four roommate students, Anna, Betty, Carla, and Daniela decided to make a “Dish washing schedule”. Every day one of the girls should do all the dishes. The challenge is to make a schedule for the next month that will spread the responsibilities among the girls as evenly as possible. At the same time the schedule should be completely random.

Solution #1: Traditional approach for mimicking pivoted report.

```WITH x AS (
SELECT FLOOR((LEVEL-1)/4) id,
LEVEL AS d,
RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual
CONNECT BY LEVEL<=32
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM x
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id
```

Sample output #1:

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
2          1          4          3
6          5          8          7
11          9         10         12
14         15         16         13
20         19         18         17
24         21         22         23
28         27         26         25
31                    30         29
```

Sample output #2 (after re-running the same query):

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
3          4          2          1
7          5          6          8
12          9         11         10
16         14         13         15
20         19         18         17
23         24         21         22
25         27         26         28
31         29         30
```

Solution #2: Using Recursive WITH clause for range generation:

```WITH x(d) AS (
SELECT 1 AS d
FROM dual
UNION ALL
SELECT d+1
FROM x
WHERE d<32
), y AS (
SELECT FLOOR((d-1)/4) id,
CASE WHEN d<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN d END d,
RANK()OVER(PARTITION BY FLOOR((d-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM x
)
SELECT MAX(CASE WHEN MOD(rk,4)=1 THEN D END) AS "Anna",
MAX(CASE WHEN MOD(rk,4)=2 THEN D END) AS "Betty",
MAX(CASE WHEN MOD(rk,4)=3 THEN d END) AS "Carla",
MAX(CASE WHEN MOD(rk,4)=0 THEN D END) AS "Daniela"
FROM y
WHERE d<=TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY id
ORDER BY id
```

Sample output #1:

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
1          4          2          3
8          7          6          5
11         10          9         12
15         14         13         16
18         19         20         17
24         22         21         23
25         27         28         26
29                    30         31
```

Sample output #2 (after re-running the same query):

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
1          4          3          2
5          6          7          8
11         12         10          9
13         15         16         14
20         19         17         18
22         23         24         21
28         26         27         25
29         30         31
```

Solution #3: Using PIVOT clause:

```SELECT "1" AS "Anna","2" AS "Betty", "3" AS "Carla", "4" AS "Daniela"
FROM (
SELECT FLOOR((LEVEL-1)/4) id,
CASE WHEN LEVEL<=TO_CHAR(LAST_DAY(SYSDATE),'DD') THEN LEVEL END AS d,
RANK()OVER(PARTITION BY FLOOR((LEVEL-1)/4) ORDER BY DBMS_RANDOM.VALUE) rk
FROM dual
CONNECT BY LEVEL<=4*CEIL(31/4)
)
PIVOT
(
MAX(d)
FOR rk IN (1,2,3,4)
)
ORDER BY id;
```

Sample output #1:

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
4          1          3          2
7          6          8          5
10          9         11         12
16         14         13         15
20         19         17         18
23         22         21         24
26         25         27         28
31                    29         30
```

Sample output #2 (after re-running the same query):

```      Anna      Betty      Carla    Daniela
---------- ---------- ---------- ----------
4          1          3          2
8          7          5          6
11         10         12          9
16         13         14         15
20         19         17         18
21         22         23         24
28         26         27         25
31         29         30
```

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 Challenge – Solutions to the 1st Puzzle

Last week we started a new contest, Puzzle of the Week. Today we publish correct answers for the 1st puzzle:

 Write a single SELECT statement that would output a calendar for the current month in a traditional tabular format (7 columns: Sun-Sat).

Solution #1: No Sub-query solution! We consider it the best solution.

To better understand the following query we suggest you to first check if you can understand Solution #3 (see below).

```SELECT MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'1', LEVEL)) SUN,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'2', LEVEL)) MON,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'3', LEVEL)) TUE,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'4', LEVEL)) WED,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'5', LEVEL)) THU,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'6', LEVEL)) FRI,
MIN(DECODE (TO_CHAR (TRUNC(SYSDATE,'MON') + LEVEL - 1, 'd'),
'7', LEVEL)) SAT
FROM DUAL
CONNECT BY LEVEL <= TO_CHAR(LAST_DAY(SYSDATE),'DD')
GROUP BY TRUNC(TRUNC(SYSDATE,'MON') + LEVEL-1, 'DAY')
ORDER BY TRUNC(TRUNC(SYSDATE,'MON') + LEVEL-1, 'DAY');```

Solution #2: Using PIVOT

```SELECT "'SUN'" SU,"'MON'" MO,"'TUE'" TU,"'WED'" WE,
"'THU'" TH,"'FRI'" FR,"'SAT'" SA
FROM
(
SELECT TRUNC(TRUNC(SYSDATE,'MON')+LEVEL-1,'DAY') WEEK_START,
TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'DD') DD,
TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'DY') DY
FROM DUAL
CONNECT BY TO_CHAR(TRUNC(SYSDATE,'MON')+LEVEL-1,'yyyymm')=
TO_CHAR(SYSDATE,'yyyymm')
)
PIVOT
(
MAX(DD)
FOR DY IN ('SUN','MON','TUE','WED','THU','FRI','SAT')
)
ORDER BY week_start;```

Solution #3: Use the power of CONNECT BY clause to generate a range of days for the current month

```WITH x AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+level-1, TRUNC(SYSDATE, 'MON'))<1
)
SELECT MAX(CASE WHEN TO_CHAR(D,'DY')='SUN' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS SUN,
MAX(CASE WHEN TO_CHAR(D,'DY')='MON' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS MON,
MAX(CASE WHEN TO_CHAR(D,'DY')='TUE' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS TUE,
MAX(CASE WHEN TO_CHAR(D,'DY')='WED' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS WED,
MAX(CASE WHEN TO_CHAR(D,'DY')='THU' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS THU,
MAX(CASE WHEN TO_CHAR(D,'DY')='FRI' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS FRI,
MAX(CASE WHEN TO_CHAR(D,'DY')='SAT' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS SAT
FROM X
GROUP BY TRUNC(D, 'DAY')
ORDER BY TRUNC(D, 'DAY')

```

Solution #4: Use existing table(s) to generate a range of days for the current month

```WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+ROWNUM-1 D
FROM emp,emp
WHERE TO_CHAR(TRUNC(SYSDATE, 'MON')+ROWNUM-1, 'YYYYMM')=TO_CHAR(SYSDATE, 'YYYYMM')
AND ROWNUM<=31
)
SELECT MAX(CASE WHEN TO_CHAR(D,'DY')='SUN' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS SUN,
MAX(CASE WHEN TO_CHAR(D,'DY')='MON' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS MON,
MAX(CASE WHEN TO_CHAR(D,'DY')='TUE' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS TUE,
MAX(CASE WHEN TO_CHAR(D,'DY')='WED' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS WED,
MAX(CASE WHEN TO_CHAR(D,'DY')='THU' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS THU,
MAX(CASE WHEN TO_CHAR(D,'DY')='FRI' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS FRI,
MAX(CASE WHEN TO_CHAR(D,'DY')='SAT' THEN TO_CHAR(D,'DD')
ELSE '  ' END) AS SAT
FROM X
GROUP BY TRUNC(D, 'DAY')
ORDER BY TRUNC(D, 'DAY')
```

Solution #5: Present each calendar week as a single column value – using LISTAGG function

```WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1
), y AS (
SELECT LISTAGG(TO_CHAR(d,'DD'), '  ') WITHIN GROUP(ORDER BY d) AS week, TRUNC(D, 'DAY') wday
FROM X
GROUP BY TRUNC(D, 'DAY')
)
SELECT CASE WHEN week LIKE '01%' THEN LPAD(week, 26)
ELSE week
END AS "SUN MON TUE WED THU FRI SAT"
FROM y
ORDER BY wday```

Solution #6: Present each calendar week as a single column value – using SYS_CONNECT_BY_PATH function

```WITH X AS (
SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1
)
SELECT CASE WHEN MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')) LIKE ' 01%' THEN
ELSE MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' '))
END " SU MO TU WE TH FR SA"
FROM x
CONNECT BY d=PRIOR d+1 AND TRUNC(d,'DAY')=TRUNC(PRIOR d, 'DAY')
GROUP BY TRUNC(d, 'DAY')
ORDER BY 1```

Solution #7: A variation of Solution #6

```SELECT CASE
WHEN MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' ')) LIKE ' 01%' THEN
ELSE MAX(SYS_CONNECT_BY_PATH(TO_CHAR(d, 'DD'), ' '))
END " SU MO TU WE TH FR SA"
FROM (SELECT TRUNC(SYSDATE, 'MON')+level-1 d
FROM DUAL
CONNECT BY MONTHS_BETWEEN(TRUNC(SYSDATE, 'MON')+LEVEL-1, TRUNC(SYSDATE, 'MON'))<1) x
CONNECT BY d=PRIOR d+1 AND TRUNC(d,'DAY')=TRUNC(PRIOR d, 'DAY')
GROUP BY TRUNC(d, 'DAY')
ORDER BY 1

```

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.

## List all employees in 2 columns based on the salary ranking.

Problem: List all employee names and their respective salaries in 2 columns based in the salary ranking (from the highest to the lowest).

Expected Result:

``` ID LEFT_NAME      LEFT_SAL RIGHT_NAME    RIGHT_SAL
--- ------------ ---------- ------------ ----------
1 KING               5000 FORD               3000
2 SCOTT              3000 JONES              2975
3 BLAKE              2850 CLARK              2450
4 ALLEN              1600 TURNER             1500
5 MILLER             1300 WARD               1250
7 JAMES               950 SMITH               800
```

Solution:
I have picked 5 best performing methods to solve this problem. The idea behind each method can be found in my book: “Oracle SQL Tricks and Workarounds”

Method/Workaround #1: Using Hierarchical Query (Level: Advanced)

```WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) RN
FROM EMP
)
SELECT  rn/2 AS id, PRIOR ename left_name, PRIOR sal left_sal, ename right_name, sal right_sal
FROM X
WHERE MOD(level,2)=0
CONNECT BY rn=1+PRIOR rn
```

Method/Workaround #2: Using Analytical Function (Level: Advanced)

```WITH X AS (
SELECT ename left_name, sal left_sal,
LEAD(ename, 1) OVER(ORDER BY sal DESC) AS right_name,
LEAD(sal, 1) OVER(ORDER BY sal DESC) as right_sal,
ROW_NUMBER() OVER(ORDER BY sal DESC) rn
from emp
)
SELECT (rn+1)/2 AS ID, left_name, left_sal,
right_name, right_sal
FROM X
WHERE MOD(rn,2)=1
ORDER BY rn
```

Method/Workaround #3: Using PIVOT Clause (Level: Advanced)

```SELECT *
FROM (SELECT CEIL(rn/2) AS ID, ename, sal, 2-MOD(rn,2) AS col_no
FROM (SELECT ename, sal, ROW_NUMBER() OVER(ORDER BY sal DESC) rn
FROM emp
)
)
PIVOT (MAX(ename) AS name,
MAX(sal)   AS sal
FOR (col_no) IN (1 AS left, 2 AS right)
)
ORDER BY 1
```

Method/Workaround #4: Using MAX function on concatenated column expression (Level: Advanced)

```WITH X AS (
SELECT LPAD(sal, 5, '0') || ename as sname, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT CEIL(rn/2) ID, SUBSTR(MAX(SNAME), 6) left_name,  TO_NUMBER(SUBSTR(MAX(SNAME), 1, 5)) left_sal,
SUBSTR(MIN(SNAME), 6) right_name, TO_NUMBER(SUBSTR(MIN(SNAME), 1, 5)) right_sal
FROM X
GROUP BY CEIL(rn/2)
ORDER BY 1
```

Method/Workaround #5: Using Self-Join (Level: Intermediate)

```WITH X AS (
SELECT ename, sal, ROW_NUMBER()OVER(ORDER BY sal DESC) rn
FROM EMP
)
SELECT B.rn/2 AS ID, a.ename AS left_name, a.sal AS left_sal,
b.ename AS right_name, b.sal AS right_sal
FROM x a LEFT JOIN x b ON a.rn+1=b.rn
WHERE mod(a.rn,2)=1
```

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