8-47 distinctオプション指定のwmsys.wm_concatを模倣

SQLパズル

strTable
ID  Val1  Val2  Val3
--  ----  ----  ----
aa  11    22    33
aa  99    22    33
aa  11    22    99
bb  44    55    66
bb  44    55    66
cc  aa    bb    cc
cc  aa    bb    cc
dd  xx    yy    zz
dd  yy    zz    xx
dd  zz    xx    yy
dd  yy    zz    xx

下記のdistinctオプション指定のwmsys.wm_concatを模倣する。

select ID,wmsys.wm_concat(distinct Val1) as Val1,
          wmsys.wm_concat(distinct Val2) as Val2,
          wmsys.wm_concat(distinct Val3) as Val3
  from strTable
group by ID
order by ID;

出力結果
ID  Val1      Val2      Val3
--  --------  --------  --------
aa  11,99     22        33,99
bb  44        55        66
cc  aa        bb        cc
dd  xx,yy,zz  xx,yy,zz  xx,yy,zz

データ作成スクリプト

create table strTable(ID,Val1,Val2,Val3) as
select 'aa','11','22','33' from dual union all
select 'aa','99','22','33' from dual union all
select 'aa','11','22','99' from dual union all
select 'bb','44','55','66' from dual union all
select 'bb','44','55','66' from dual union all
select 'cc','aa','bb','cc' from dual union all
select 'cc','aa','bb','cc' from dual union all
select 'dd','xx','yy','zz' from dual union all
select 'dd','yy','zz','xx' from dual union all
select 'dd','zz','xx','yy' from dual union all
select 'dd','yy','zz','xx' from dual;

SQL

col Val1 for a10
col Val2 for a10
col Val3 for a10

--■■■XMLの関数を使う方法■■■
select ID,
Rtrim(substr(replace(replace(XMLAgg(XMLElement("dummy",Val1) order by Val1)
                            ,'</dummy>'),'<dummy>',','),2),',') as Val1,
Rtrim(substr(replace(replace(XMLAgg(XMLElement("dummy",Val2) order by Val2)
                            ,'</dummy>'),'<dummy>',','),2),',') as Val2,
Rtrim(substr(replace(replace(XMLAgg(XMLElement("dummy",Val3) order by Val3)
                            ,'</dummy>'),'<dummy>',','),2),',') as Val3
from (select ID,
      case Row_Number() over(partition by ID,Val1 order by 1) when 1 then Val1 end as Val1,
      case Row_Number() over(partition by ID,Val2 order by 1) when 1 then Val2 end as Val2,
      case Row_Number() over(partition by ID,Val3 order by 1) when 1 then Val3 end as Val3
        from strTable)
group by ID
order by ID;

--■■■model句を使う方法(10g以降)■■■
select ID,
LTrim(max(Val1),',') as Val1,LTrim(max(Val2),',') as Val2,LTrim(max(Val3),',') as Val3
from (select ID,Val1,Val2,Val3
        from strTable
       model
      partition by(ID)
      dimension by(Row_Number() over(partition by ID order by 1) as soeji)
      measures(cast(case Row_Number() over(partition by ID,Val1 order by 1)
                    when 1 then Val1 end as varchar2(20)) as Val1,
               cast(case Row_Number() over(partition by ID,Val2 order by 1)
                    when 1 then Val2 end as varchar2(20)) as Val2,
               cast(case Row_Number() over(partition by ID,Val3 order by 1)
                    when 1 then Val3 end as varchar2(20)) as Val3)
      rules(
      Val1[any] order by soeji = nvl2(Val1[cv(soeji)]
                                     ,Val1[cv(soeji)-1] || ',' || Val1[cv(soeji)]
                                     ,Val1[cv(soeji)-1]),
      Val2[any] order by soeji = nvl2(Val2[cv(soeji)]
                                     ,Val2[cv(soeji)-1] || ',' || Val2[cv(soeji)]
                                     ,Val2[cv(soeji)-1]),
      Val3[any] order by soeji = nvl2(Val3[cv(soeji)]
                                     ,Val3[cv(soeji)-1] || ',' || Val3[cv(soeji)]
                                     ,Val3[cv(soeji)-1])))
group by ID
order by ID;

解説

インラインビューで連結対象外の文字列をnullに変換しています。

---