10-87 カンマ区切りの文字列で存在チェック

SQLパズル

テーブル
Val
----
A10
B20
C30A
D40
A10
C30

テーブルのデータをカンマ区切りで連結して変数に代入する。
ただし、一度、変数に連結した文字と同じ文字は、連結対象外とする

出力結果
--------------------
A10,B20,C30A,D40,C30

SQL

--■■■instr関数を使う方法■■■
declare
    WillOut Varchar2(200);
begin
    for rec_Work in (select 'A10' as Val from dual
               union all select 'B20'  from dual
               union all select 'C30A' from dual
               union all select 'D40'  from dual
               union all select 'A10'  from dual
               union all select 'C30'  from dual) Loop
        if instr(',' || WillOut || ',',',' || rec_Work.Val || ',') = 0 then
            WillOut := WillOut || ',' || rec_Work.Val;
        end if;
    end Loop;
    DBMS_Output.Put_Line(substr(WillOut,2));
end;
/

--■■■正規表現を使う方法(10g以降)■■■
declare
    WillOut Varchar2(200);
begin
    for rec_Work in (select 'A10' as Val from dual
               union all select 'B20'  from dual
               union all select 'C30A' from dual
               union all select 'D40'  from dual
               union all select 'A10'  from dual
               union all select 'C30'  from dual) Loop
        if RegExp_Like(WillOut,'(^|,)' || rec_Work.Val || '($|,)') then
            null;
        else
            WillOut := WillOut || ',' || rec_Work.Val;
        end if;
    end Loop;
    DBMS_Output.Put_Line(substr(WillOut,2));
end;
/

--■■■model句を使う方法(10g以降)■■■
with WorkView as (select 'A10' as Val from dual
union all select 'B20'  from dual
union all select 'C30A' from dual
union all select 'D40'  from dual
union all select 'A10'  from dual
union all select 'C30'  from dual)
select max(Val) as Val
from (select Val
        from WorkView
      model
      dimension by (RowNum as soeji)
      measures(cast(Val as varchar2(200)) as Val)
      rules(Val[any] order by soeji = case when cv(soeji) = 1 then Val[cv()]
                                           when instr(',' || Val[cv()-1] || ',',',' || Val[cv()] || ',') > 0
                                           then Val[cv()-1]
                                           else Val[cv()-1] || ',' || Val[cv()] end));

解説

distinctで文字の重複を排除できるなら、
distinctで排除したほうがいいでしょう。

---