2011年8月12日金曜日

行単位で持っているデータを列として出力するSQL

ひとつの 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')

2011年8月8日月曜日

ネットワークドライブ上のXLSファイルを開くのが遅い

タイトルの通り WIndows7 X Office2007(Excel) の環境で旧Excelファイル(xls)開くのが遅い。
とにかく遅い。原因はよくわからないけどxlsファイル開くときだけが半端なく重いので
拡張子「xls」を開くときの挙動を見て不要な機能のせいで重いのなら無効化にしてやろう
と思って、下記参考。
Windows 7にてファイルの関連づけを編集したい - Microsoft Answers
Windows VistaやWindows 7では、OSの機能として、関連付けに関する詳細設定はできなくなりました
ってことで却下。

じゃあ自前PCなのでOfficeのHotfixとかで出てないかな、で下記参考。
An Office program is slow or may appear to stop responding (hang) when you open a file from a network location
When you try to open a file from a network location in one of the Microsoft Office programs listed in the "Applies To" section of this article, the Office program may run very slowly or may appear to stop responding (hang).
ってことでよさげ。実際hangってこたぁないけど
ちょっと時間ないからコレで今日半日使ってみてどうかな、と

<2011/08/09 追記>
たぶん解決した。Excelブック開くのが5分の1位になったよっと。
最初のMicrosoft Fix it 50637 消さないと。

全然違うことで下記ページをみてて当たり前のことに気がついた
マイクロソフト セキュリティ アドバイザリ (2501584): Microsoft Office 向けの Microsoft Office ファイル検証機能の公開
セキュリティの設定が影響してるんだ たぶん。
セキュリティ云々より開くのがクソ遅いと仕事にならないので
Excelリボンのオフィスボタン?(正式名所知らん)から
Excelのオプション→セキュリティセンター→セキュリティセンターの設定→信頼できる場所 の設定で
「プライベートネットワーク上にある信頼できる場所を許可する(推奨しません)」にチェック入れて
社内のネットワークドライブを場所に追加。
コレで早くなったよ