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

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

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

### 5 thoughts on “Puzzle of the Week #19 – Department Salary Report”

1. Taroon Ray August 1, 2016 / 11:46 pm

WITH A AS(
SELECT SAL, EMPNO, ENAME, DEPTNO FROM EMP
)
SELECT ENAME,
(SELECT SAL FROM EMP WHERE EMP.EMPNO = A.EMPNO AND A.DEPTNO = 10) AS “10”,
(SELECT SAL FROM EMP WHERE EMP.EMPNO = A.EMPNO AND A.DEPTNO = 20) AS “20”,
(SELECT SAL FROM EMP WHETE EMP.EMPNO = A.EMPNO AND A.DEPTNO = 30) AS “30”
FROM A;

2. Krishna Jamal August 1, 2016 / 11:53 pm

SELECT * FROM
(SELECT Deptno, Ename, Sal FROM Emp)
PIVOT (SUM(Sal) FOR Deptno IN (10, 20, 30));

3. Krishna Jamal August 2, 2016 / 4:36 am

SELECT E.Ename,
(SELECT E1.Sal FROM Emp E1 WHERE E1.Empno=E.Empno AND E1.Deptno=10) “10”,
(SELECT E2.Sal FROM Emp E2 WHERE E2.Empno=E.Empno AND E2.Deptno=20) “20”,
(SELECT E3.Sal FROM Emp E3 WHERE E3.Empno=E.Empno AND E3.Deptno=30) “30”
FROM Emp E;

4. praneeth August 5, 2016 / 2:02 am

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;