ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

SQL Server Management StudioのビューでデータをPIVOT表示する。

leave a comment »

データベースのテーブル構造は「正規化」されたものでなければならない。たとえば各社員の年度ごとの販売実績のようなデータを管理するためには、正規化されたデータ構造は次のようになる。このテーブルでは6桁の社員番号で社員を管理し、年度は西暦で整数値とし、販売実績を個数を示す整数値としている。また社員の年度データは1件のみとし、社員番号と年度の2フィールドを主キーとして同一年度の同一社員に複数データの重複がないようにしている。

SQL_Server_001_PIVOT_001

<Fig.1 : 社員の年度ごとの販売実績を管理するテーブル>

SQL_Server_001_PIVOT_002

<Fig.2 : 販売実績テーブルのデータを表示する>

もちろんデータを管理し運用するために正規化は必要なのだが、データを利用するためには見やすい表の形式にする必要がある。たとえば縦に社員番号を並べ、横に各年度の販売数を列挙するという方法で、Excelなどで表形式でデータを作るときにはこのようにするはずだ。このように正規化されたデータをクロス集計し縦と横に項目を展開して表示することを一般に「PIVOT(ピボット)」と呼ぶ。

SQL_Server_001_PIVOT_003

<Fig.3 : PIVOT表示された販売実績のデータ>

このPIVOT形式の表示は、SQL Serverのビューで作成することができる。ただSQL Server Management StudioでPIVOTビューを作成するときには、ある手順が必要となる。まず新しいビューを作り「販売実績」テーブルをビューに加える。そして「社員番号」フィールドにチェックを入れて表示し、グループ化する。

SQL_Server_001_PIVOT_004

<Fig.4 : 「販売実績」テーブルを加えたビューを作成し「社員番号」フィールドを選びグループ化した>

ビューをPIVOT形式にするには、フィールドリストの「列」に式を書くか、またはSQLビューに直接SQL文を書く。まずフィールドリストの「列」に式を書く方法でやってみよう。選んだ「社員番号」の次の列にCASE演算子を使った以下の式を書く。

SUM(CASE WHEN 年度 = 2011 THEN 販売数 END)

この式の意味は、もし「年度」が2011ならば、販売数のデータを選択して合計する、というものである。CASE文は条件を示す「WHEN」で始まり、次に結果をあらわす「THEN」を示し、最後に「END」で終わらなければならない。最後のENDがなければエラーになるので注意しよう。プログラミングに慣れた人ほど「END」を忘れがちと思われる。ビューのフィールドリストに式を書きEnterキーを押して決定すると次のようになる。できたらSQLを実行して結果を得よう。

SQL_Server_001_PIVOT_005

<Fig.5 : フィールドリストにCASE演算子を使った式を直接記入する>

SQL_Server_001_PIVOT_006

<Fig.6 : フィールドリストにCASE演算子気を使って書いた式を決定しSQLを実行した>

SQLの結果ビューには「社員番号」のリストと2011年度の販売数が表示されている。しかしフィールド名は自動的に付けられた「Expr1」となっている。またフィールドリストに書いた式からSUMが除かれ、CASE WHEN 年度 = 2011 THEN 販売数 END、という式に変わってしまった。ではSUMはどこに行ったかといえば、グループ化の項目としてセットされている。しかしSQLビューのSQL文を見ると、ちゃんと書いたとおりの式になっていることが確認できる。

では次にフィールド名の「Expr1」を「2011」に変えよう。それにはフィールドリストの「別名」の「Expr1」を「2011」に変えればいい。このとき数字で始まる別名を付けると、Management Studioはフィールド名であることを明示的に表すために自動的に四角括弧をつけ[2011]のようにしてくれる。設定できたらSQLを実行して結果を得よう。

SQL_Server_001_PIVOT_007

<Fig.7 : フィールドリストの別名を変更してSQLを実行した>

ところで、似た式を作るとき、既にできた式をコピーして部分的に変更することをよくするだろう。そこで「列」に作った式をコピーし、次のリストにペーストして年度の条件だけ変えることを試してみる。式を書いて決定し、SQLを実行しよう。すると「SQLの実行エラーです」のエラーが表示され、式に示した列が集計関数またはGROUP BY句に含まれていないとなる。

SQL_Server_001_PIVOT_008

<Fig.8 : フィールドリストの式をコピーして次のフィールドにペーストし、年度を変えた>

SQL_Server_001_PIVOT_009_w_edit

<Fig.9 : SQLを実行しようとするがエラーになる>

ここでフィールドリストの「グループ化」のところでSUM集計関数を指定すればよいと考えるが、リストを開いてもSUM選択肢はない。また手書きでSUMを記述しても「エントリはリストの項目に一致していなければなりません。」のエラーが表示され記述することができない。

SQL_Server_001_PIVOT_010

<Fig.10 : フィールドリストのグループ化のリストにはSUM選択肢がない>

SQL_Server_001_PIVOT_011

<Fig.11 : フィールドリストのグループ化に直接SUM集計関数を書こうとする>

SQL_Server_001_PIVOT_012_w_edit

<Fig.12 : SUM集計関数がグループ化リストの選択肢にないためエラーになる>

つまり列に式を記述して決定するとManagement Studioは式を解釈してSUM集計関数を式から外してグループ化項目に移すのだが、逆にSUM集計関数を外した式を記述してグループ化項目にSUMを記述することができないのだ。そこで元の「列」に書いた式にSUM集計関数を付け加えることにする。「列」にSUM集計関数を加えてEnterキーで決定すると、Management StudioはSUM集計関数の部分を式から外してグループ化に移し、SQLも実行することができる。「別名」も年度を示す2012に変更しよう。

SQL_Server_001_PIVOT_013

<Fig.13 : 列リストの式にSUM集計関数を加える>

SQL_Server_001_PIVOT_014

<Fig.14 : 列リストの式にSUM集計関数を加えて決定するとSUMは「グループ化」に移される>

SQL_Server_001_PIVOT_015

<Fig.15 : SQLを実行し期待通りのPIVOTを得た>

このようにPIVOTを得るための式を記述するとき、ある意味Management Studioが勝手な操作をしてくれるのだが、最もわかりやすいのは直接SQLビューでクエリを付け加えることだ。自動的に生成したSQL文を参考にし、SELECT文に次の式を付け加えるといい。なお式でフィールドを追加するときはカンマで区切る必要がある。

SUM(CASE WHEN 年度 = 2013 THEN 販売数 END) AS [2013]

SQL_Server_001_PIVOT_016

<Fig.16 : SQLビューに直接式を記述してフィールドを加える>

SQL_Server_001_PIVOT_017

<Fig.17 : SQLビューの式を決定し、SQLを実行して期待通りの結果を得る>

これで期待通りのPIVOTビューができた。できたビューは保存しておこう。

Written by Yoshio Matsumoto

2013年11月9日 @ 11:40 PM

コメントを残す

以下に詳細を記入するか、アイコンをクリックしてログインしてください。

WordPress.com ロゴ

WordPress.com アカウントを使ってコメントしています。 ログアウト / 変更 )

Twitter 画像

Twitter アカウントを使ってコメントしています。 ログアウト / 変更 )

Facebook の写真

Facebook アカウントを使ってコメントしています。 ログアウト / 変更 )

Google+ フォト

Google+ アカウントを使ってコメントしています。 ログアウト / 変更 )

%s と連携中

%d人のブロガーが「いいね」をつけました。