8-34 複数列をPivot

SQLパズル

empPivot
Dname   DNo  Job
------  ---  ------
Clark    10  Mgrs
King     10  Prez
Miller   10  Clerks
Adams    20  Clerks
Ford     20  Anals
Jones    20  Mgrs
Smith    20  Clerks
Scott    20  Anals
Allen    30  Sales
Blake    30  Mgrs
James    30  Clerks
Martin   30  Sales
Turner   30  Sales
Ward     30  Sales

下記の出力結果になるようにPivotを行う。

出力結果
D10     D20     D30     Clerks  Mgrs    Prez    Anals   Sales
------  ------  ------  ------  ------  ------  ------  ------
Clark   null    null    null    Clark   null    null    null
King    null    null    null    null    King    null    null
Miller  null    null    Miller  null    null    null    null
null    Adams   null    Adams   null    null    null    null
null    Ford    null    null    null    null    Ford    null
null    Jones   null    null    Jones   null    null    null
null    Scott   null    null    null    null    Scott   null
null    Smith   null    Smith   null    null    null    null
null    null    Allen   null    null    null    null    Allen
null    null    Blake   null    Blake   null    null    null
null    null    James   James   null    null    null    null
null    null    Martin  null    null    null    null    Martin
null    null    Turner  null    null    null    null    Turner
null    null    Ward    null    null    null    null    Ward

SQLクックブックのレシピ12.16を参考にさせていただきました

データ作成スクリプト

create table empPivot(Dname,DNo,Job) as
select 'Clark' ,10,'Mgrs'   from dual union
select 'King'  ,10,'Prez'   from dual union
select 'Miller',10,'Clerks' from dual union
select 'Adams' ,20,'Clerks' from dual union
select 'Ford'  ,20,'Anals'  from dual union
select 'Jones' ,20,'Mgrs'   from dual union
select 'Scott' ,20,'Anals'  from dual union
select 'Smith' ,20,'Clerks' from dual union
select 'Allen' ,30,'Sales'  from dual union
select 'Blake' ,30,'Mgrs'   from dual union
select 'James' ,30,'Clerks' from dual union
select 'Martin',30,'Sales'  from dual union
select 'Turner',30,'Sales'  from dual union
select 'Ward'  ,30,'Sales'  from dual;

SQL

select
decode(DNo,10,Dname) as D10,
decode(DNo,20,Dname) as D20,
decode(DNo,30,Dname) as D30,
decode(Job,'Clerks',Dname) as Clerks,
decode(Job,'Mgrs'  ,Dname) as Mgrs,
decode(Job,'Prez'  ,Dname) as Prez,
decode(Job,'Anals' ,Dname) as Anals,
decode(Job,'Sales' ,Dname) as Sales
  from empPivot
order by DNo,Dname;

解説

11gなら新機能のPivotの使いどころかもしれませんね。

---