10-56 結合して階層問い合わせ

SQLパズル

TABLE1
ColA  ColB  Name
----  ----  ----
   1     1  ccc1
   2     2  ccc2
   3     3  ccc3
   4     1  ccc4

TABLE2
ColB  ColD
----  ----
   1  ddd1
   1  ddd2
   1  ddd3
   2  ddd4
   2  ddd5
   3  ddd6

TABLE1とTABLE2から以下の出力をする。

出力結果
ColA  Name  Concat_ColD
----  ----  --------------
   1  ccc1  ddd1,ddd2,ddd3
   2  ccc2  ddd4,ddd5
   3  ccc3  ddd6
   4  ccc4  ddd1,ddd2,ddd3

データ作成スクリプト

create table table1(
ColA number(1),
ColB number(1),
Name char(4));

insert into table1 values(1,1,'ccc1');
insert into table1 values(2,2,'ccc2');
insert into table1 values(3,3,'ccc3');
insert into table1 values(4,1,'ccc4');

create table table2(
ColB number(1),
ColD char(4));

insert into table2 values(1,'ddd1');
insert into table2 values(1,'ddd2');
insert into table2 values(1,'ddd3');
insert into table2 values(2,'ddd4');
insert into table2 values(2,'ddd5');
insert into table2 values(3,'ddd6');
commit;

SQL

col Concat_ColD for a16

--■■■count関数で葉か判断■■■
select ColA,Name,substr(sys_connect_by_path(ColD,','),2) as Concat_ColD
from (select a.ColA,a.Name,b.ColD,
      Row_Number() over(partition by a.ColA order by b.ColD) as Rank,
      count(*) over(partition by a.ColA) as MaxLevel
       from TABLE1 a,TABLE2 b
      where a.ColB = b.ColB)
where Level = MaxLevel
Start With Rank = 1
connect by Prior ColA = ColA
       and Prior Rank = Rank-1;

--■■■Connect_by_IsLeafで葉か判断(10g以降)■■■
select ColA,Name,substr(sys_connect_by_path(ColD,','),2) as Concat_ColD
from (select a.ColA,a.Name,b.ColD,
      Row_Number() over(partition by a.ColA order by b.ColD) as Rank
       from TABLE1 a,TABLE2 b
      where a.ColB = b.ColB)
where Connect_by_IsLeaf = 1
Start With Rank = 1
connect by Prior ColA = ColA
       and Prior Rank = Rank-1;

--■■■wmsys.wm_concatを使う方法■■■
select a.ColA,a.Name,
wmsys.wm_concat(b.ColD) as Concat_ColD
  from TABLE1 a,TABLE2 b
 where a.ColB = b.ColB
group by a.ColA,a.Name
order by a.ColA;

--■■■XMLの関数を使う方法■■■
select a.ColA,a.Name,
substr(replace(replace(XMLAgg(XMLElement("dummy",b.ColD) order by b.ColD),
                      '</dummy>'),'<dummy>',','),2) as Concat_ColD
  from table1 a,table2 b
 where a.ColB = b.ColB
group by a.ColA,a.Name
order by a.ColA;

解説

XMLの関数を使う方法がお勧めですね。

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

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

---