1列N行のデータを「区切り文字を使って1列1行」にして返すアドホックなクエリ

SQL Server

N行のデータを1行にまとめたい！カーソル使ったら出来るのは分かるけどクエリ1個で出来ないの？
という要望があったのでとりあえず再帰クエリを使って書いてみました。

use [テスト] 

create table [Hoge] ( 
  [val] nchar(1) 
) 

insert into [Hoge] 
select N'あ' union select N'か' union select N'さ' 
union select N'た' union select N'な' union select N'は' 
union select N'ま' union select N'や' union select N'ら' 

select * from [Hoge]; 

with [seq_data] ( [val], [seq] ) as ( 
  select 
    cast([val] as nvarchar(4000)), 
    row_number() over ( order by [val] ) 
  from [Hoge] 
), [cte] ( [val], [seq] ) as ( 
  select top 1 [val], [seq] from [seq_data] order by [seq] 
  union all 
  select 
    [cte].[val] + ',' + [seq_data].[val], [seq_data].[seq] 
  from 
    [cte] inner join [seq_data] on ( 
	  [cte].[seq] + 1 = [seq_data].[seq] 
	) 
) 

select max([val])from [cte] 
go 

drop table [Hoge]


クエリ的には、[seq_data] がまず再帰クエリをするための連番を振っています。
次の [cte] で再帰クエリを行い、列の値を "," 区切りで結合しています。
最後に、MAX を使い全部結合したデータを取得します。


このケースなら、カーソルを使えるならそっちの方が良さそうですけどね。
環境無いので試してませんが、Oracle で書き換えるなら row_number が rownum になり、top 1 の変わりに rownum + サブクエリ使って、共通テーブル式の再帰は 11gR2 からなので、それ以前の場合は、connect by で実現出来るかな？