作成者別アーカイブ: Yoshio Matsumoto

Yoshio Matsumoto について

Data Platform MVP based in Kobe, Japan. Teaching IT and Chemistry in HighSchool, and construct high school database system in internal using InfoPath and SQL Server and Visual Studio C#.

SQL Server : サーバーサイドでデータの変更日付をテーブルに自動的に記録するために、AFTER UPDATEトリガを利用する。変更データの絞込みにはサブクエリではなく in 句を使う。

「SQL Server : サーバーサイドでデータの作成日付をテーブルに自動的に記録するために、フィールドの列のプロパティで規定値を設定し、INSTEAD OF INSERTトリガを利用する」では、テーブルのフィールドに「作成日」のフィールドを作り、データの生成日と時間を自動的に記録する方法を説明した。データの生成日と時間をサーバー側で記録するには、フィールドのプロパティに既定値を設定する方法と、INSTEAD OF INSERTトリガで強制的にデータを与える方法があった。では、既に生成しているデータの「更新日」をSQL Server側で自動的に記録するにはどうすればよいだろう。これもテーブルに「トリガ」を設定することで実現できるが、少し工夫が必要だ。

まず次のようなテーブルを作ろう。

——————————————————

フィールド名  データ型   備考
ID        int      主キー、IDENTITY(はい)、シード(1)、増分(1)
データ      int
作成日     datetime
更新日     datetime

——————————————————

SQLSV_blog_update_datetime_001_mid_640

<Fig.1 : サンプルの「テスト」テーブルを作る>

テーブルができたら、いくつかサンプルのデータを作っておこう。

SQLSV_blog_update_datetime_003_mid_640

<Fig.2 : 「テスト」テーブルにサンプルのデータを作っておく>

次に、この「テスト」テーブルに次のクエリでAFTER UPDATEトリガを設定する。

——————————————————

CREATE trigger trig_02 on テスト after update

as

update テスト set 更新日 = getdate() where ID = (select ID from inserted)

——————————————————

SQLSV_blog_update_datetime_002_mid_640

<Fig.3 : 「テスト」テーブルにトリガを設定する>

SQLSV_blog_update_datetime_004_mid_640

<FIg.4 : 「テスト」テーブルにトリガを設定した>

このトリガは、「テスト」テーブルにUPDATE文が発行されたとき、変更されたデータが入っている「inserted」一時テーブルのIDを調べ、そのIDと同じIDのデータに対して「更新日」のフィールド値を gerdate() 関数で現在の日付と時間を取得して更新するというものだ。

では、このトリガを設定した「テスト」テーブルのデータを更新してみよう。次のクエリを試してみる。

——————————————————

UPDATE テスト SET データ = 101 where データ = 100

——————————————————

SQLSV_blog_update_datetime_005_mid_640

<Fig.5 : トリガを設定した「テスト」テーブルに UPDATE 文を実行する>

UPDATE 文は1件のデータを更新するものだが、処理結果のメッセージが2行表示されている。トリガが動いたのだ。ではテーブルのデータを確認してみよう。

SQLSV_blog_update_datetime_006_mid_640

<Fig.6 : データを更新した「テスト」テーブルのデータを確認する>

トリガによって「更新日」のデータが自動的に記録されたことがわかる。だが、実はこのトリガには問題がある。試しに、次のクエリを実行してみよう。このクエリでは、データが 800 のデータを更新しているが、サンプルデータには 800 のデータが2件あるのだ。

——————————————————

UPDATE テスト SET データ = 801 where データ = 800

——————————————————

SQLSV_blog_update_datetime_007_mid_640

<Fig.7 : 「テスト」テーブルの2件のデータを更新しようとしてエラーになった>

トリガに設定した UPDATE 文はサブクエリだ。サブクエリでは複数の値を処理することができない。これでは困るので、トリガを変更する。変更するには、テーブルを展開してトリガを表示し、右クリックで「変更」をクリックする。

SQLSV_blog_update_datetime_010_mid_640

<Fig.8 : 「テスト」テーブルに設定したトリガを変更する>

——————————————————

update テスト set 更新日 = getdate() where ID in (select ID from inserted)

——————————————————

= でつないだサブクエリを in 句に変更するのだ。

SQLSV_blog_update_datetime_011_mid_640

<Fig.9 : トリガのサブクエリを in 句に変更する>

変更できたら、もういちどデータを2件変更する UPDATE 文を実行してみよう。うまくいくはずだ。

SQLSV_blog_update_datetime_012_mid_640

<Fig.10 : データを2件変更する UPDATE 文を実行する>

変更したデータを確認しよう。

SQLSV_blog_update_datetime_013_mid_640

<Fig.10 : UPDATE 文を変更したテーブルを確認する>

このように更新日の日付と時間をサーバー側で自動的に記録するには、テーブルにトリガを設定すればよい。そのとき、変更をうけたデータを絞り込むにサブクエリではなくて in 句を利用する。サブクエリでは複数のデータを更新する UPDATE 文でエラーがおこるためだ。

広告

SQL Server : サーバーサイドでデータの作成日付をテーブルに自動的に記録するために、フィールドの列のプロパティで規定値を設定し、INSTEAD OF INSERTトリガを利用する

データを管理する際に、そのデータがいつ作られ、いつ更新されたかを記録しておくことが求められる場合がある。これらの日付を記録するため、次のようにテーブルに「作成日」と「更新日」のフィールドを持たせておくことにする。どちらのフィールドも「datetime型」としておこう。

SQL_storedatetime_001_mid_640

<fig.1 : テーブル「Table_3」に「作成日」と「更新日」のフィールドを作成した>

このテーブルに新しいデータを生成し、「作成日」の値を記録するとき、クライアント側で明示的に処理を行うなら次のようなSQL文になるだろう。このとき、getdate()はSQL Serverから日付を取得する関数だ。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘ほげほげ’,getdate())

————————————————————————

SQL_storedatetime_002_mid_640

<fig.2 : 明示的にSQL文によってデータ作成日を登録した>

SQL_storedatetime_003_mid_640

<fig.3 : Table_3に作成日が登録された>

すでに生成したデータを更新し、「更新日」の値を記録するとき、クライアント側で明示的に処理を行うなら次のようなSQL文になるだろう。

————————————————————————

update Table_3 set サンプル値 = ‘ほにゃらら’,更新日 = getdate() where サンプル値 = ‘ほげほげ’

————————————————————————

SQL_storedatetime_004_mid_640

<fig.4 : Table_3のデータ「ほげほげ」を「ほにゃらら」に更新し、明示的にSQL文によってデータ作成日を登録した>

SQL_storedatetime_005_mid_640

<fig.5 : Table_3のデータを更新し、更新日が登録された>

新しいデータを作成したときに自動的に「作成日」が記録されるようにするには、フィールドのプロパティで「規定値」を設定する方法がある。規定値に「getdate()」関数を設定しておけばいい。規定値を設定すれば、SQL文で明示的に日付を与えなくてもテーブルには自動的に日付が入るようになる。

SQL_storedatetime_006_mid_640

<fig.6 : Table_3の「作成日」フィールドのプロパティで「規定値」を「getdate()」に設定した>

フィールドに規定値を設定すると、次のSQL文で新しいデータを生成すると「作成日」の値が自動的に記録される。

————————————————————————

insert into Table_3(サンプル値) values(‘規定値でほげ’)

————————————————————————

SQL_storedatetime_007_mid_640

<fig.7 : Table_3に「作成日」を明示的に指定せずにデータを生成した>

SQL_storedatetime_008_mid_640

<fig.8 : Table_3に「作成日」を明示的に指定せずにデータを生成し、規定値が記録された>

このように規定値を使って自動的に値を与えることは一般的だが、この方法ではうまくいかない場合がある。いくら規定値を指定していても、規定値はあくまでも値が示されないときのものであって、ユーザーが明示的に値を与えると規定値は無視されユーザーの指定した値が記録されるのだ。たとえばユーザーが次のSQL文を使うと「記録日」が期待どおりの「規定値」にならない。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘昔の日付で出ています’,’1961/8/13′)

————————————————————————

SQL_storedatetime_009_mid_640

<fig.9 : 「作成日」に規定値を設定したTable_3にSQLで明示的に日付を指定してデータを生成するSQL文を実行した>

SQL_storedatetime_010_mid_640

<fig.10 : 「作成日」に規定値を設定したTable_3にSQLで明示的に日付を指定して過去の日付でデータを生成した>

データを管理するためには、ユーザーが「作成日」に勝手な値を指定できないようにしたほうがいい。そこでユーザーが「作成日」に明示的な日付を設定しても、それを無効にしてサーバーサイドで日付を強制的に設定される方法を考えてみよう。それにはテーブルにINSERTトリガを設定する方法がある。INSERTトリガは、テーブルにデータがINSERTされたときに自動的に実行されるストアドプロシージャだ。

INSERTトリガが実行されるタイミングは2つある。ひとつはINSERTが実行された後にトリガが実行されるAFTER INSERT、もうひとつはINSERTが実行される前にトリガが実行されるINSTEAD OF INSERTだ。INSTEAD OF INSERTはその名前からわかるように、実際のINSERT文に代わって実行されるトリガだ。ためしに次のSQL文でトリガをテーブルに設定してみよう。トリガを作成するには、create triggerを使う。

————————————————————————

create trigger trig_01 on Table_3 instead of insert
as
insert into Table_3(サンプル値,作成日) values(‘トリガでほげ’,GETDATE())

————————————————————————

このトリガを設定するSQL文で、create triggerの次にくる「trig_01」はトリガ名だ。わかりやすい名前を考えて自分で考えてつけることになる。その後onに続いてどのテーブルに対して設定するのかの「テーブル名」を記述している。そしてinstead of insertがトリガの種類だ。次にasに続けて、INSERTに代わって実行させたいSQL文を記述している。トリガを作成したら、左のペイン「オブジェクトエクスプローラー」の「Table_3」を展開すると「トリガ」のところに「trig_01」という名前のトリガができていることがわかる。

SQL_storedatetime_014_mid_640

<fig.11 : create trigger文でTable_3にINSTEAD OF INSERTトリガを作成した>

これでTable_3に何か値をinsertしようとしても、代わりにINSTEAD OF INSERTトリガが働き、あらかじめ決められた内容のデータしか生成しなくなる。たとえば次のようなSQL文をTable_3に実行してみよう。

————————————————————————

insert into Table_3(サンプル値,作成日) values(‘どうなる’,’1961/8/13′)

————————————————————————

SQL_storedatetime_015_mid_640

<fig.12 : INSTEAD OF INSERTトリガを設定したTable_3に明示的にinsert文を実行する>

ひとつのinsert文しか実行していないのに、処理結果が2つあらわれている。トリガが動いたのだ。

SQL_storedatetime_016_mid_640

<fig.13 : INSTEAD OF INSERTトリガを設定したTable_3に明示的にinsert文を実行した結果>

Table_3を見ると、明示的に実行されたINSERT文は無視され、トリガによって強制的にデータがinsertされたことがわかる。「作成日」はgetdate()で得られた日付になっている。

ここで「作成日」が強制的にgetdate()の値になったことはよいが、「サンプル値」が決まった値では困る。「サンプル値」はもとのinsert文で示された値でなければいけない。このときINSERTトリガでは、もとのinsert文によって与えられたデータは、一時的なテーブル「INSERTED」に保存されている。トリガを変更し、INSERTEDテーブルの値を利用して「サンプル値」を取得しよう。トリガを変更するには、ALTER TRIGGERを使う。

————————————————————————

alter trigger trig_01 on Table_3 instead of insert
as
insert into Table_3(サンプル値,作成日) select サンプル値,getdate() from inserted

————————————————————————

このINSTEAD OF INSERTトリガで、「サンプル値」はinsert文によって一時テーブルinsertedに記録された「サンプル値」を得、「作成日」はgetdate()で与えている。

SQL_storedatetime_017_mid_640

<fig.14 : alter triggetによってINSTEAD OF INSERTトリガを変更した>

SQL_storedatetime_018_mid_640

<fig.15 : INSTEAD OF INSERTトリガを変更したテーブルにINSERT文を実行した>

SQL_storedatetime_019_mid_640

<fig.16 : 「サンプル値」をinserted一時テーブルから取得し「作成日」はgetdate()で明示的に値を与えた結果>

このように、テーブルにデータを生成するとき「作成日」を与えたいとき、ひとつはフィールドのプロパティで「規定値」を設定する方法があり、さらにユーザーによる「作成日」の明示的な指定を避けたいときは、INSTEAD OF INSERTトリガを設定し、insertされた値を強制的に変更する手法を使うことができる。INSTEAD OF INSERTトリガだけでなく、トリガは他にもいろいろな自動化処理をするために使えるので、データの整合性をとったり履歴を記録したりすることに使うなど、工夫のしがいがあるところだ。

SQL Server : フィールドにIDENTITYを設定して自動的に連番が設定されるようにしたテーブルでIDENTITY_INSERTをONにして明示的に数値を指定したデータをINSERTする

データを管理するとき、自動的に連番が設定されるようにしたい場合がある。これは例えばMicrosoft Accessでいう「オートナンバー型」のフィールドに相当する。Microsoft Accessの「オートナンバー型」は、データを生成するときに何も値を入れず、自動的に連番が設定されるフィールドだ。テーブルを構成するとき、このように自動的に連番が割り当てられるフィールドを作っておくと、その連番値自体に特段の意味はなくても、データを管理する場面で有効になる。システムの運用をはじめると、さまざまな想定外のことがおこる。間違ったデータを大量に作ってしまい特定の範囲のデータを全部まとめて修正しなければならない、重複したデータを作ってしまい後から作ったデータを削除しなければならない、ある一定期間に行われた処理に誤りがあり一括してデータを修正しなければならない、といったようなこことだ。これらの運用時に、データが生成された順番がわかっていると処理しなければならないデータの範囲を絞り込む重要な手掛かりになる。

Microsoft Accessには次のように「オートナンバー型」というデータ型がある。

SQL_identity_002_mid_640

<Fig.1 : Miceosoft Access 2013でフィールドのデータ型をオートナンバー型にする>

しかしSQL ServerにはAccessのようなオートナンバー型というデータ型はない。SQL ServerでAccessのオートナンバー型のように、数値が連番で自動的に設定されるようにするには、データ型を「bigint」、「decimal」、「int」、「numeric」、「smallint」、「tinyint」のような整数の性質を持っているものとし、列のプロパティで「IDENTITYの指定」の「(IDである)」を「はい」にし、「IDの増分」と「IDENTITYシード」を設定する。「IDENTITYシード」は最初に割り当てられる数値を意味し、「IDの増分」はデータが増えるたびに加えられる値を意味する。次の例は「IDENTITYシード」を10、「IDの増分」を3として作ったテーブルにデータをいくつか追加した例だ。

SQL_identity_004_mid_640

<fig.2 : IDフィールドのIDENTITYを指定し、IDENTITYシードを10、IDの増分を3と設定する>

SQL_identity_006_mid_640

<fig.3 : IDフィールドにIDENTITYを指定したテーブルにデータを3件追加した>

なおIDENTITYの設定は「float」、「real」のような実数型では指定することができない。また「money」、「smallmoney」でも指定することができない。

IDENTITYを指定したフィールドには明示的に値を指定することはできず、自動的に値が割り当てられるだけになる。例えば次のようなINSERT文は実行できない。

——————————————————————

insert into Table_1(ID,[values]) values(20,’testinsert’)

——————————————————————

SQL_identity_007_mid_640

<fig.4 : IDENTITYを指定したフィールドに明示的に値を指定したINSERT文を実行しエラーとなった>

IDENTITYを指定したフィールドは、たとえば次のように明示的に値を指定せずにINSERT文を実行することになるだろう。

——————————————————————

insert into Table_1([values]) values(‘testinsert’)

——————————————————————

SQL_identity_008_mid_640

<fig.5 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した>

SQL_identity_009_mid_640

<fig.6 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した結果>

INSERTされたデータのIDは、フィールドに指定した「IDの増分」だけ増えた値が自動的に割り当てられている。もちろんこれは期待どおりの結果だが、データの保守管理上はこのままでは困る場合が出てくる。たとえばあるテーブルのデータを別のテーブルに一時的に退避し、後に戻したいといった場合だ。データを退避するため「table_1」と同じフィールド構造をもつ「Table_2」を作っておくが、「Table_2」の「ID」フィールドのIDENTITYは設定しない。そして次のクエリのように、「Table_1」のデータを「Table_2」にコピーし、「Table_1」のデータを消去するが、そのあと、「Table_2」のデータを「Table_1」の戻すときにはエラーとなる。

——————————————————————

insert into Table_2(ID,[values]) select ID,[values] from Table_1
delete Table_1
insert into Table_1(ID,[values]) select ID,[values] from Table_2 –(エラーになる)

——————————————————————

SQL_identity_010_mid_640

<fig.7 : IDENTITYが設定されているフィールドに明示的に値を指定してINSERTしようとしてエラーになった>

このエラーを回避する最もわかりやすい方法は、「Table_1」の「ID」列のIDENTITY指定を一時的に解除することだ。しかし一時的ではあれテーブルのデザインを変更すると、そのあいだに他のユーザーが正しい方法でデータを挿入しようとしたときにエラーになるだろう。自分だけが使うデータベースであるとか、システムが小規模で他に与える影響がないと考えられる場合はいいかもしれないが、望ましいことではない。

このようにIDENTITYが設定されたフィールドに明示的に値を与えたいとき、IDENTITY_INSERTをONにする方法がある。先のエラーになったINSERT文の前後にIDENTITY_INSERT文を使おう。例えば次のクエリはエラーにならず成功する。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) select ID,[values] from Table_2
set identity_insert Table_1 off

——————————————————————

SQL_identity_011_mid_640

<fig.8 : IDENTITY_INSERTの設定によりINSERTが成功した>

set identity_insert Table_1 onによってIDENTITYを設定したフィールドにも明示的に値を与えることができるようになり、set identity_insert Table_1 offでデフォルトの状態に戻る。

たとえば次のようにIDに「20」という値を明示的に挿入するクエリも実行することができる。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) values(20,’ID20testdata’)
set identity_insert Table_1 off

——————————————————————

SQL_identity_012_mid_640

<fig.9 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした>

SQL_identity_013_mid_640

<fig.10 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした結果>

このように、テーブルにIDENTITYを設定したIDフィールドを持たせることはよくやることだが、IDENTITYを指定したフィールドには通常は明示的な値を与えることはないし、それはできないのだが、identity_insertをon、offすることによって明示的に値を与えることができ、これはデータの管理の手法として知っておくとよい。

SQL Serverセミナー初級2 2013年12月14日(土) 10:30~11:50 三宮

SQL Server初心者のために、「初級1」に続いてユーザー定義関数やストアドプロシージャの作り方やSQL文の応用を実習により学ぶセミナーを開催します。SQL Serverに興味はあるが使ったことのない人、エクセルやアクセスで業務データの処理をしているがSQL Serverに変更したい人、SQL Serverとはどんなものか体験したい人などに役立つ内容です。会場や設備の都合上、人数に制約がありますので、電子メールで事前にお申し込みください。

当日は同じ部屋で9:00~10:20に「SQL Serverセミナー初級1」を行います。連続して参加していただいてもかまいません。

日時:平成25年12月14日(土) 10:30~11:50(80分間)
場所:センタープラザ西館貸会議室12号室(下記地図参照)
    〒650-0021 神戸市中央区三宮町2丁目11番1 センタープラザ西館6階
            ● TEL 078-331-5311     ● FAX 078-331-9618
内容:ユーザー定義関数、ストアドプロシージャの作り方と使い方、SQL文の応用
形式:ノートパソコンを使った実習(ハンズオン)、質疑応答
    SQL ServerとManagement Studioをインストールしたノートパソコンをご持参ください。
    こおセミナーに対応するSQL Serverのバージョンは以下の通りです。
        SQL Server 2000、SQL Server 2005、SQL Server 2008、SQL Server 2012
    (ご自身でノートパソコンが用意できない方は事前にメールで問い合わせください。)
定員:10名
運営費:500円
主催:神戸SQL Server Users Group、特定非営利活動法人 情報技術相互支援協会
申し込み:下記の要領で電子メールをお送りください。
         送信先電子メールアドレス kobesql20131214@kssug.com
         件名「20131214 SQL Serverセミナー初級2(三宮)申し込み」
         本文 参加者のお名前(ふりがな)、所属、メールアドレス、連絡先電話番号を
             お書きください。
      ※受付が完了したらメールで返信いたします。メールの返信がない場合は、メール
        アドレスをご確認いただき再度送信ください。

map_000_thumb

danmenzu_002_thumb

SQL Serverセミナー初級1 2013年12月14日(土) 9:00~10:20 三宮

SQL Server初心者のために、テーブルやビューの作り方や簡単なSQL文などSQL Serverを使うための基本要素を実習により学ぶセミナーを開催します。SQL Serverに興味はあるが使ったことのない人、エクセルやアクセスで業務データの処理をしているがSQL Serverに変更したい人、SQL Serverとはどんなものか体験したい人などに役立つ内容です。会場や設備の都合上、人数に制約がありますので、電子メールで事前にお申し込みください。

このセミナーの内容は、2013年11月09日(土)に行ったものと同じです。

日時:平成25年12月14日(土) 9:00~10:20(80分間)
場所:センタープラザ西館貸会議室12号室(下記地図参照)
    〒650-0021 神戸市中央区三宮町2丁目11番1 センタープラザ西館6階
            ● TEL 078-331-5311     ● FAX 078-331-9618
内容:Management Studioの使い方、テーブル作成、データベースダイアグラム、
    ビューとリレーションシップ、データの型、簡単なSQL文
形式:ノートパソコンを使った実習(ハンズオン)、質疑応答
    SQL ServerとManagement Studioをインストールしたノートパソコンをご持参ください。
    こおセミナーに対応するSQL Serverのバージョンは以下の通りです。
        SQL Server 2000、SQL Server 2005、SQL Server 2008、SQL Server 2012
    (ご自身でノートパソコンが用意できない方は事前にメールで問い合わせください。)
定員:10名
運営費:500円
主催:神戸SQL Server Users Group、特定非営利活動法人 情報技術相互支援協会
申し込み:下記の要領で電子メールをお送りください。
         送信先電子メールアドレス kobesql20131214@kssug.com
         件名「20131214 SQL Serverセミナー初級1(三宮)申し込み」
         本文 参加者のお名前(ふりがな)、所属、メールアドレス、連絡先電話番号を
             お書きください。
      ※受付が完了したらメールで返信いたします。メールの返信がない場合は、メール
        アドレスをご確認いただき再度送信ください。

map_000_thumb

danmenzu_002_thumb

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

データベースのテーブル構造は「正規化」されたものでなければならない。たとえば各社員の年度ごとの販売実績のようなデータを管理するためには、正規化されたデータ構造は次のようになる。このテーブルでは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ビューができた。できたビューは保存しておこう。

SQL Serverセミナー初級1 2013年11月09日(土) 10:00~11:30 三宮

SQL Server初心者のために、テーブルやビューの作り方や簡単なSQL文などSQL Serverを使うための基本要素を実習により学ぶセミナーを開催します。SQL Serverに興味はあるが使ったことのない人、エクセルやアクセスで業務データの処理をしているがSQL Serverに変更したい人、SQL Serverとはどんなものか体験したい人などに役立つ内容です。会場や設備の都合上、人数に制約がありますので、電子メールでお申し込みください。

日時:平成25年11月9日(土) 10:00~11:30(90分間)
場所:センタープラザ西館貸会議室12号室(下記地図参照)
    〒650-0021 神戸市中央区三宮町2丁目11番1 センタープラザ西館6階
            ● TEL 078-331-5311     ● FAX 078-331-9618
内容:Management Studioの使い方、テーブル作成、データベースダイアグラム、ビューとリレーション
    シップ、データの型、簡単なSQL文
形式:ノートパソコンを使った実習(ハンズオン)、質疑応答
    SQL ServerとManagement Studioをインストールしたノートパソコンをご持参ください。セミナー
    に対応するSQL Serverのバージョンは以下の通りです。
        SQL Server 2000、SQL Server 2005、SQL Server 2008、SQL Server 2012
    (ご自身でノートパソコンが用意できない方は事前にメールで問い合わせください。)
定員:10名
運営費:500円
主催:神戸SQL Server Users Group、特定非営利活動法人 情報技術相互支援協会
申し込み:下記の要領で電子メールをお送りください。
         送信先電子メールアドレス kobesql20131109@kssug.com
         件名「20131109 SQL Serverセミナー初級1(三宮)申し込み」
         本文 参加者のお名前(ふりがな)、所属、メールアドレス、連絡先電話番号をお書きください。
      ※受付が完了したらメールで返信いたします。メールの返信がない場合は、メールアドレスを
        ご確認いただき再度送信ください。

神戸サンセンタープラザ西館貸会議室_map_000
      神戸サンセンタープラザ西館貸会議室_danmenzu_002