10-268 最小数に合わせるpivot

SQLパズル

scott.empテーブル
ENAME   JOB
------  ---------
FORD    ANALYST
SCOTT   ANALYST
ADAMS   CLERK
JAMES   CLERK
MILLER  CLERK
SMITH   CLERK
BLAKE   MANAGER
CLARK   MANAGER
JONES   MANAGER
KING    PRESIDENT
ALLEN   SALESMAN
MARTIN  SALESMAN
TURNER  SALESMAN
WARD    SALESMAN

JOBがCLERK,SALESMAN,MANAGER
のnameでpivotを行う。
CLERKの行数が4
SALESMANの行数が4
MANAGERの行数が3
なので出力結果は3行になります。

出力結果
CLERK   SALESMAN  MANAGER
------  --------  -------
ADAMS   ALLEN     BLAKE
JAMES   MARTIN    CLARK
MILLER  TURNER    JONES

こちらを参考にさせていただきました(英語)

SQL

--■■■having句で固定で3を指定する方法■■■
select
max(decode(job,'CLERK'   ,ENAME)) as CLERK,
max(decode(job,'SALESMAN',ENAME)) as SALESMAN,
max(decode(job,'MANAGER' ,ENAME)) as MANAGER
from (select ENAME,job,
      Row_Number() over(partition by job order by ENAME) as Rn
        from scott.emp
       where job in('CLERK','SALESMAN','MANAGER'))
group by Rn
having count(*) = 3
order by Rn;

--■■■論理積を判定する方法■■■
select
max(decode(job,'CLERK'   ,ENAME)) as CLERK,
max(decode(job,'SALESMAN',ENAME)) as SALESMAN,
max(decode(job,'MANAGER' ,ENAME)) as MANAGER
from (select ENAME,job,
      Row_Number() over(partition by job order by ENAME) as Rn
        from scott.emp
       where job in('CLERK','SALESMAN','MANAGER'))
group by Rn
having max(decode(job,'CLERK'   ,ENAME)) is not null
   and max(decode(job,'SALESMAN',ENAME)) is not null
   and max(decode(job,'MANAGER' ,ENAME)) is not null
order by Rn;

解説

model句でも考えましたが、
model句は、行のフィルタには向いてないですね。

---