から、ひとつの 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')
0 件のコメント:
コメントを投稿