トップページに戻る    次のSQLパズルへ    前のSQLパズルへ

3-20 文字列の縦横変換

SQLパズル

テーブル
Val
------
oracle
wendy

文字列を縦横変換して出力する

出力結果
Val     縦文字
------  ------
oracle  o
oracle  r
oracle  a
oracle  c
oracle  l
oracle  e
wendy   w
wendy   e
wendy   n
wendy   d
wendy   y


SQL

col 縦文字 for a8

--■■■結合を使う方法■■■
with WorkView as (
select 'oracle' as Val from dual
union select 'wendy'  from dual)
select a.Val,substr(a.Val,b.Counter,1) as 縦文字
  from WorkView a,(select RowNum as Counter from all_catalog) b
 where length(a.Val) >= b.Counter
order by a.Val,b.Counter;

--■■■階層問い合わせを使う方法■■■
with WorkView as (
select 'oracle' as Val from dual
union select 'wendy'  from dual),
CounterView as (
select RowNum as Counter
from all_catalog
where RowNum <= (select max(Length(Val)) from WorkView))
select a.Val,substr(a.Val,b.Counter,1) as 縦文字
from WorkView a,CounterView b
where b.Counter <= Length(a.Val)
start with b.Counter = 1
connect by prior a.Val = a.Val
       and prior b.Counter = b.Counter - 1
order by a.Val,Level;


解説

結合を使う方法が分かりやすいと思います