ひとつの id に対して複数の val を行単位で保有するテーブル (表1)
から、ひとつの id に対して複数の val を列単位で抽出 (表2) するSQLのメモ
表2の取得は2種類
その1.列として出力する内容を固定で定義
その2.列として出力する内容が可変でもOKなように
テスト用テーブル作成するSQL と 表1の取得
-- テスト用テーブル作成するSQL:
if exists (
select * from tempdb.dbo.sysobjects
where id = object_id('tempdb.dbo.#hoge')
)
begin
drop table #hoge
end
select * into #hoge from (
select 'a' id,'o' val union all
select 'a','x' union all
select 'a','-' union all
select 'a','+' union all
select 'a','/' union all
select 'b','o' union all
select 'c','x')t
-- 表1の内容を出力するSQL:
select * from #hoge
表2の内容を出力するSQL その1
-- 表2の内容を出力するSQL:
-- 列として出力する内容が固定
select id
, max(case row_num when 1 then val else '' end ) val1
, max(case row_num when 2 then val else '' end ) val2
, max(case row_num when 3 then val else '' end ) val3
, max(case row_num when 4 then val else '' end ) val4
, max(case row_num when 5 then val else '' end ) val5
from (
select id, val, row_number() over (partition by id order by id) row_num from #hoge
) a
group by id
表2の内容を出力するSQL その2
-- 表2の内容を出力するSQL:
-- 列として出力する内容が可変
declare @cntLoop int
, @cntColumn int
, @variableColFields as varchar(8000)
, @variableColumnName as varchar(10)
set @cntLoop = 1
set @variableColFields = ''
set @variableColumnName = 'val'
select @cntColumn = max(rownum) from (
select row_number() over (partition by id order by id) rownum
from #hoge
) a
while @cntLoop <= @cntColumn
begin
if @cntLoop > 1
begin
set @variableColFields = @variableColFields + ','
end
set @variableColFields = @variableColFields
+ ' max(case row_num when ' + convert(varchar,@cntLoop)
+ ' then val else '''' end ) ' + @variableColumnName + convert(varchar,@cntLoop) + char(13) + char(10)
set @cntLoop = @cntLoop + 1
end
exec ('select id ,' + @variableColFields + '
from (
select id, ' + @variableColumnName + ', row_number() over (partition by id order by id) row_num from #hoge
) a
group by id')