クロス集計のようなかんじのクエリについて

Question

こんにちは。

社内のアプリ開発を考えています。

ユーザテーブルと別のテーブルを多対多でつなぐテーブルを用意する予定です。
その際そのテーブルを以下のようにし、列を一つまたは二つ追加する予定です。
UserID, OtherTablePK, date, name
UserID と OtherTablePK は最終的に必ず関連づくことになる予定ですが
それまでの特定の段階では関連づいているものとそうでないものがあります。
関連付けが行われるときにその関連付けが行われた日付とそれを行うことになった人の名前で行を挿入する予定です。

これをデータとして参照するために、行方向はユーザテーブル（PK：UserID）内のある列名を
列方向は別のテーブル（PK：OtherTablePK）内のある列名をそれぞれタイトルとし、date と name 列の内容を表示させたいのですがどのようなSQLを書けばいいでしょうか。CLRでがんばってストアドを書くなりしたほうがいいのでしょうか？

Answer 1

すみません、文章だけではやりたいことのイメージがピンとこないので、エクセルか何かで数行のサンプルデータとアウトプットのイメージを作って頂き、

それをキャプチャしてアップして頂けるともう少し回答が付きやすいかと。

Answer 2

もしくはわかる範囲までSQL文を書いて、わからない部分を文章で説明してはどうでしょうか？

# なんとなくUNIONでつなぐのかな？

Answer 3

ありがとうございます。

たしかにわかりにくいですね。

説明忘れとなりますが、ASP.NET での実装となります。
以下のような感じでテーブルを作り、テーブルで表示したいと考えています。
とりあえずWebForms で将来的には MVC を考えています。

どちらを行にするか列にするかはまだ悩んでいます。
また名前を表示する数は一定ではありません。
もう一つは数がどんでもないことになるのでページング機能をつけることになると考えています。
関連付けができていない分にかんしては、空白にするか
「-」などを表示するかを考えています。

ご意見よろしくお願いします。

Answer 4

行は当然複数行となりますが、列数も同様に可変にしたい、という事でしょうか？

Answer 5

ありがとうございます。

Contents テーブルにはたくさん登録される見込みですが
見やすさの観点から表を作る際の列数は最大5～10列の一定にして
「次の10件」のような感じでみれるようにしたいと思っています。

Contents テーブルにはカテゴリ定義用の列も用意するのですが
そちらで絞り込むことを可能にしたいと思っています。
カテゴリによっては、1ページ10件ずつのところ、5件しかヒットしないという可能性もあります。
この場合は可変になります。

ただ、余った5列は空白列でもいいかもしれません。

Answer 6

念のため…SQL Serverは見た目は提供しませんよ。クエリーに対して結果を返すだけです。クエリー結果の見せ方はまた別の問題です。
その上で、どこまでSQL Serverに対するクエリーで処理して、プログラム側でクエリー結果を（必要であれば加工して）どのように表示するか、その分岐点を決める必要があります。

ASP.NETでの実装なのはわかりますが、その点、理解しないまま質問を投げているように見受けられたので。例えば「余った5列は空白列でもいいかもしれません」をSQL Serverフォーラムに書き込んだ場合、そのような結果を得るクエリーを必要としていると受け取るべきところですが、それはプログラム側で処理すべきことと思いますし、この質問に含めるべきではない話題です。

その上で、２点。

PIVOTという機能があります。これを使えば希望するような表形式の結果を得ることができます。ただし、サンプルコードを読むとわかりますが、「タイトル１」「タイトル２」などの情報がクエリー式の時点で必要になります。もちろんストアドプロシージャとしてクエリー式を生成してからEXECしても構いませんが、クエリー結果をプログラム側で読み取ることも考えなければなりません。

１ページ何件という話題が挙げられているので一応。ORDER BY句にはOFFSETとFETCHがあるため、希望されているようなページングが可能です。

…ですが、

簡単な形式で取り出し、ASP.NET側で並べる処理をした方が実装として簡単だったりしませんか？

Answer 7

> 説明忘れとなりますが、ASP.NET での実装となります。
> 以下のような感じでテーブルを作り、テーブルで表示
> したいと考えています。

クロス集計部分はストアドプロシージャで行い、そのス
トアドプロシージャを起動して DB からデータを取得し
て表示するのは ASP.NET の SqlDataSource + GridView
などのサーバコントロールを使って可能だと思います。

以下のページの例のような感じ（あくまで感じ）でどう
でしょうか。

PIVOT の使用
http://surferonwww.info/BlogEngine/post/2010/08/07/Use-of-PIVOT.aspx

> とりあえずWebForms で将来的には MVC を考えています。

違いを理解してますか？　全く別物だと思ったほうがいい
です。一旦サーバーコントロールを使って作ってしまった
Web Forms を MVC に移行するということはあり得ません。

メリット/デメリットをよく理解して、最初にどちらで行く
か決めないとダメです。

Answer 8

こんにちは、SweetSmile さん
フォーラムオペレータの佐伯 玲 です。

その後の状況はいかがでしょうか？
みなさんから寄せられている情報をご確認いただいてその結果や、うまくいかなかったり不明であった点があればその内容をご返信くださいませ。

宜しくお願い致します。
__________________________
日本マイクロソフト株式会社 フォーラム オペレータ 佐伯 玲

Answer 9

こんにちは。

その後、いろいろどうしようかとなやみ
結局、Contents テーブルと User テーブル・Relationsテーブルからそれぞれ必要な情報を DataTable として取り出し、

foreach (Usersテーブルの行)
{
    foreach (Contentsテーブルの各行）
    {
        //それぞれの行のIDを使って、Relations DataTable内の有無を確認など
    }  
}

という感じでコードを書いて表を作成することで対応できました。

一発でクエリかけてコントロールにバインドするだけでできればいいなぁとおもってたんですが
これぐらいは面倒がらずにできるようにならないとだめですね。。。。。

ありがとうございました。