3-21 文字列のソート

SQLパズル

strTable
Val
------
dabc
mysql
tiger
oracle
wendy

文字列をアルファベットの昇順にソートして出力する

出力結果
Val     ソート後
------  --------
dabc    abcd
mysql   lmqsy
oracle  acelor
tiger   egirt
wendy   denwy

データ作成スクリプト

create table strTable(Val) as
select 'dabc'   from dual union
select 'mysql'  from dual union
select 'tiger'  from dual union
select 'oracle' from dual union
select 'wendy'  from dual;

SQL

col ソート後 for a20

--■■■ListAggを使わない方法■■■
with CounterView as (
select RowNum as Counter
from all_catalog
where RowNum <= (select max(Length(Val)) from strTable))
select Val,replace(sys_connect_by_path("1文字",','),',') as ソート後
from (select a.Val,
      substr(a.Val,b.Counter,1) as "1文字",
      Row_Number() over(partition by a.Val order by substr(a.Val,b.Counter,1)) as rn
      from strTable a,CounterView b
      where b.Counter <= Length(a.Val))
where Connect_by_IsLeaf = 1
start with rn = 1
connect by prior Val = Val
       and prior rn = rn - 1
order by Val;

--■■■ListAggを使う方法(11gR2以降)■■■
select a.Val,
ListAgg(substr(a.Val,b.cnt,1))
within group (order by substr(a.Val,b.cnt,1)) as ソート後
  from strTable a
  Join (select RowNum as cnt from dict) b
    on length(a.Val) >= cnt
group by a.Val;

解説

Row_Number関数で文字の昇順に連番をふってます。

---