10-304 stringAggをPivot

SQLパズル

sysconnT
dt       item  code
-------  ----  ----
2009/04  X     A
2009/04  X     B
2009/04  X     C
2009/04  Y     A
2009/04  Y     B
2009/04  Y     D
2009/05  X     A
2009/06  X     A
2009/06  X     B
2009/06  X     D
2009/06  Y     B
2009/06  Y     C
2009/06  Y     E
2009/12  X     B
2010/03  X     C
2010/03  X     D
2010/03  X     E

itemごとdtごとにcodeを連結させた文字列を
下記の形にPivotする。

出力結果
item   2009/04  2009/05  2009/06  2009/12  2010/03
-----  -------  -------  -------  -------  -------
X      ABC      A        ABD      B        CDE
Y      ABD      null     BCE      null     null

データ作成スクリプト

create table sysconnT(dt,item,code) as
select '2009/04','X','A' from dual union all
select '2009/04','X','B' from dual union all
select '2009/04','X','C' from dual union all
select '2009/04','Y','A' from dual union all
select '2009/04','Y','B' from dual union all
select '2009/04','Y','D' from dual union all
select '2009/05','X','A' from dual union all
select '2009/06','X','A' from dual union all
select '2009/06','X','B' from dual union all
select '2009/06','X','D' from dual union all
select '2009/06','Y','B' from dual union all
select '2009/06','Y','C' from dual union all
select '2009/06','Y','E' from dual union all
select '2009/12','X','B' from dual union all
select '2010/03','X','C' from dual union all
select '2010/03','X','D' from dual union all
select '2010/03','X','E' from dual;

SQL

col item for a5
col "2009/04" for a7
col "2009/05" for a7
col "2009/06" for a7
col "2009/12" for a7
col "2010/03" for a7

--■■■階層問い合わせを使う方法■■■
select item,
max(decode(dt,'2009/04',replace(sys_connect_by_path(code,','),','))) as "2009/04",
max(decode(dt,'2009/05',replace(sys_connect_by_path(code,','),','))) as "2009/05",
max(decode(dt,'2009/06',replace(sys_connect_by_path(code,','),','))) as "2009/06",
max(decode(dt,'2009/12',replace(sys_connect_by_path(code,','),','))) as "2009/12",
max(decode(dt,'2010/03',replace(sys_connect_by_path(code,','),','))) as "2010/03"
  from (select dt,item,code,
        Row_Number() over(partition by dt,item order by code) as rn
          from sysconnT)
 where connect_by_isleaf=1
Start With rn=1
connect by prior rn = rn-1
       and prior item = item
       and prior dt = dt
group by item;

--■■■wmsys.wm_concat関数を使う方法■■■
select item,
replace(wmsys.wm_concat(decode(dt,'2009/04',code)),',') as "2009/04",
replace(wmsys.wm_concat(decode(dt,'2009/05',code)),',') as "2009/05",
replace(wmsys.wm_concat(decode(dt,'2009/06',code)),',') as "2009/06",
replace(wmsys.wm_concat(decode(dt,'2009/12',code)),',') as "2009/12",
replace(wmsys.wm_concat(decode(dt,'2010/03',code)),',') as "2010/03"
  from sysconnT
group by item;

--■■■XMLの関数と正規表現を使う方法(10g以降)■■■
select item,
RegExp_Replace(XMLAgg(decode(dt,'2009/04',XMLElement("dummy",code)) order by code),'</?dummy>') as "2009/04",
RegExp_Replace(XMLAgg(decode(dt,'2009/05',XMLElement("dummy",code)) order by code),'</?dummy>') as "2009/05",
RegExp_Replace(XMLAgg(decode(dt,'2009/06',XMLElement("dummy",code)) order by code),'</?dummy>') as "2009/06",
RegExp_Replace(XMLAgg(decode(dt,'2009/12',XMLElement("dummy",code)) order by code),'</?dummy>') as "2009/12",
RegExp_Replace(XMLAgg(decode(dt,'2010/03',XMLElement("dummy",code)) order by code),'</?dummy>') as "2010/03"
from sysconnT
group by item
order by item;

解説

階層問い合わせを使う方法では、脳内で木をイメージすると分かりやすいでしょう。

Oracleの階層問い合わせ1 (start with句,connect by句)

10-17 Group_Concat関数を階層問い合わせで模倣
10-18 Group_Concat関数をXMLの関数で模倣
10-218 wmsys.wm_concat

---