10-75 複数行をカンマ区切りで出力

SQLパズル

HogeTable
Col1  Col2  Col3
----  ----  ------
P1    A     101102
P1    A     103104
P1    B     102105
P2    A     101105
P2    B     101103
P2    C     102104

Col1ごとに、一行にまとめて、
カンマ区切りで出力する(order by Col2,Col3の順序でまとめる)

出力結果
Col1  Col2   Col3
----  -----  -----------------------------
P1    A,A,B  |101,|102,|103,|104,|102,|105
P2    A,B,C  |101,|105,|101,|103,|102,|104

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

データ作成スクリプト

create table HogeTable(
Col1 char(2),
Col2 char(1),
Col3 char(6));

insert into HogeTable values('P1','A','101102');
insert into HogeTable values('P1','A','103104');
insert into HogeTable values('P1','B','102105');
insert into HogeTable values('P2','A','101105');
insert into HogeTable values('P2','B','101103');
insert into HogeTable values('P2','C','102104');
commit;

SQL

col col1 for a4
col col2 for a5
col col3 for a30

--■■■階層問い合わせを使う方法■■■
select Col1,
max(substr(sys_connect_by_path(Col2,','),2)) as Col2,
max(replace(substr(
    sys_connect_by_path('|' || substr(Col3,1,3) || '||' || substr(Col3,-3),',')
    ,2),'||',',|')) as Col3
from (select Col1,Col2,Col3,
      Lag(RowID) over(partition by Col1 order by Col2,Col3) as LagRowID
      from HogeTable)
start with LagRowID is null
connect by Prior RowID = LagRowID
group by Col1
order by Col1;

--■■■XMLの関数を使う方法■■■
select Col1,
substr(
replace(
    replace(XMLAgg(XMLElement("禁則文字",Col2) order by Col2,Col3),'</禁則文字>'),
    '<禁則文字>',',')
,2) as col2,
substr(
replace(
    replace(XMLAgg(XMLElement("禁則文字",'|' || substr(Col3,1,3) || ',|' || substr(Col3,-3))
            order by Col2,Col3),'</禁則文字>'),
    '<禁則文字>',',')
,2) as col3
from HogeTable
group by Col1
order by Col1;

解説

MySQLのGroup_Concat関数は、
階層問い合わせ、もしくは、XMLの関数で、
模倣できます。

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

---