10-17 Group_Concat関数を階層問い合わせで模倣

SQLパズル

KeyListテーブル
Key  Val
---  ---
AAA   12
AAA   2A
AAA   32
AAA   44
BBB   11
BBB   33
BBB   8S

KeyListテーブルをKeyごとに、
Valの昇順にカンマ区切りで文字列結合して、出力する。

出力結果
KEY  ConcatVal
---  -----------
AAA  12,2A,32,44
BBB  11,33,8S

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

データ作成スクリプト

create table KeyList(
Key char(3),
Val char(2),
primary key (Key,Val));

insert into KeyList values('AAA','12');
insert into KeyList values('AAA','2A');
insert into KeyList values('AAA','32');
insert into KeyList values('AAA','44');
insert into KeyList values('BBB','11');
insert into KeyList values('BBB','33');
insert into KeyList values('BBB','8S');
commit;

SQL

col ConcatVal for a20

--■■■count関数で葉か判断1■■■
select Key,SubStr(sys_connect_by_path(Val,','),2) as ConcatVal
from (select Key,Val,
      Row_Number() over(partition by Key order by Val) as Rank,
      count(Key) over(partition by Key) as MaxLevel
      from KeyList)
where Level = MaxLevel
start with Rank = 1
connect by Prior Key = Key
       and Prior Rank = Rank-1
order by Key;

--■■■count関数で葉か判断2■■■
select Key,SubStr(sys_connect_by_path(Val,','),2) as ConcatVal
from (select Key,Val,
      Lag(RowID) over(partition by Key order by Val) as LagRowID,
      count(Key) over(partition by Key) as MaxLevel
      from KeyList)
where Level = MaxLevel
start with LagRowID is null
connect by Prior RowID = LagRowID
order by Key;

--■■■Connect_by_IsLeafで葉か判断1(10g以降)■■■
select Key,SubStr(sys_connect_by_path(Val,','),2) as ConcatVal
from (select Key,Val,
      Row_Number() over(partition by Key order by Val) as Rank
      from KeyList)
where Connect_by_IsLeaf = 1
start with Rank = 1
connect by Prior Key = Key
       and Prior Rank = Rank-1
order by Key;

--■■■Connect_by_IsLeafで葉か判断2(10g以降)■■■
select Key,SubStr(sys_connect_by_path(Val,','),2) as ConcatVal
from (select Key,Val,
      Lag(RowID) over(partition by Key order by Val) as LagRowID
      from KeyList)
where Connect_by_IsLeaf = 1
start with LagRowID is null
connect by Prior RowID = LagRowID
order by Key;

--■■■グループ化する方法■■■
select Key,max(SubStr(sys_connect_by_path(Val,','),2)) as ConcatVal
from (select Key,Val,
      Lag(RowID) over(partition by Key order by Val) as LagRowID
      from KeyList)
start with LagRowID is null
connect by Prior RowID = LagRowID
group by Key
order by Key;

解説

MySQLのGroup_Concat関数を、階層問い合わせで模倣できます。

10-18 Group_Concat関数をXMLの関数で模倣
10-56 結合して階層問い合わせ

マニュアルにのってない非公式な方法として
wmsys.wm_concatを使う方法もあります。
10-218 wmsys.wm_concat

model句を使う方法もあります。
model句04 wmsys.wm_concatの代用
model句22 MySQLのgroup_concatもどき

---

ORACLE-BASE - String Aggregation Techniques(英語)
stragg in 10gR2(英語)

---