5-37 複数行を1列に変換

SQLパズル

数値テーブル
 a   b   c
--  --  --
 1   9   8
 2   2   7
 3   1   2
 4   5   6

数値テーブルを1行1列にまとめて出力する。

出力結果
ConcatVal
-----------------------
1,9,8,2,2,7,3,1,2,4,5,6

データ作成スクリプト

create Table 数値テーブル(
a number(1),
b number(1),
c number(1));

insert into 数値テーブル values(1,9,8);
insert into 数値テーブル values(2,2,7);
insert into 数値テーブル values(3,1,2);
insert into 数値テーブル values(4,5,6);
commit;

SQL

col ConcatVal for a30

--■■■count関数で葉か判断1■■■
select Replace(SubStr(sys_connect_by_path(ConcatString,','),2),'-',',') as ConcatVal
from (select to_char(a) || '-' || to_char(b) || '-' || to_char(c) as ConcatString,
      Row_Number() over(order by a,b,c) as Rank,
      count(a) over() as MaxLevel
      from 数値テーブル)
where Level = MaxLevel
start with Rank = 1
connect by Prior Rank = Rank-1;

--■■■count関数で葉か判断2■■■
select Replace(SubStr(sys_connect_by_path(ConcatString,','),2),'-',',') as ConcatVal
from (select to_char(a) || '-' || to_char(b) || '-' || to_char(c) as ConcatString,
      Lag(RowID) over(order by a,b,c) as LagRowID,
      count(a) over() as MaxLevel
      from 数値テーブル)
where Level = MaxLevel
start with LagRowID is null
connect by Prior RowID = LagRowID;

--■■■Connect_by_IsLeafを使って葉か判断1(10g以降)■■■
select Replace(SubStr(sys_connect_by_path(ConcatString,','),2),'-',',') as ConcatVal
from (select to_char(a) || '-' || to_char(b) || '-' || to_char(c) as ConcatString,
      Row_Number() over(order by a,b,c) as Rank
      from 数値テーブル)
where Connect_by_IsLeaf = 1
start with Rank = 1
connect by Prior Rank = Rank-1;

--■■■Connect_by_IsLeafを使って葉か判断2(10g以降)■■■
select Replace(SubStr(sys_connect_by_path(ConcatString,','),2),'-',',') as ConcatVal
from (select to_char(a) || '-' || to_char(b) || '-' || to_char(c) as ConcatString,
      Lag(RowID) over(order by a,b,c) as LagRowID
      from 数値テーブル)
where Connect_by_IsLeaf = 1
start with LagRowID is null
connect by Prior RowID = LagRowID;

解説

階層問い合わせを使うと、再帰的にSQLを発行できます。

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

マニュアルにのってない非公式な方法として
wmsys.wm_concatを使う方法もあります。
10-218 wmsys.wm_concat