SQL2000でのユーザ定義関数作成に関する質問です。

質問

開発環境はvb6, SQLサーバ2000です。

下記のようなテーブルから実行結果に記入してある結果を得れるようなユーザ定義関数をおしえていただきたいです。

分類マスタ(KEY=分類CD)
--------------------------------------------
分類CD　 |分類名　
--------------------------------------------
1　　　　|魚類　　　|
--------------------------------------------
2　　　　|野菜　　　|
--------------------------------------------
3　　　　|牛肉　　　|
--------------------------------------------
4　　　　|豚肉　　　|
--------------------------------------------

親テーブル(KEY=会員CD)
--------------------------------------------
会員CD　 |会員名　|出身地　|
--------------------------------------------
1　　　　|田中　　|大阪　　|
--------------------------------------------
2　　　　|佐藤　　|東京　　|
--------------------------------------------
3　　　　|中井　　|鹿児島　|
--------------------------------------------

子テーブル(KEY=会員CD, 枝番)
--------------------------------------------
会員CD　 |枝番　 |分類CD　 |処理日
--------------------------------------------
1　　　　|1　　　|1　　　　|1/1
--------------------------------------------
1　　　　|2　　　|2　　　　|1/2
--------------------------------------------
1　　　　|3　　　|2　　　　|1/3
--------------------------------------------
1　　　　|4　　　|4　　　　|1/4
--------------------------------------------
1　　　　|5　　　|2　　　　|1/5
--------------------------------------------
1　　　　|6　　　|3　　　　|1/6
--------------------------------------------
1　　　　|7　　　|4　　　　|1/7
--------------------------------------------
2　　　　|1　　　|1　　　　|1/1
--------------------------------------------
2　　　　|2　　　|2　　　　|1/2
--------------------------------------------
2　　　　|3　　　|1　　　　|1/3
--------------------------------------------
2　　　　|4　　　|1　　　　|1/4
--------------------------------------------
2　　　　|5　　　|4　　　　|1/5
--------------------------------------------
2　　　　|6　　　|1　　　　|1/6
--------------------------------------------
2　　　　|7　　　|4　　　　|1/7
--------------------------------------------
3　　　　|1　　　|3　　　　|1/1
--------------------------------------------
3　　　　|2　　　|3　　　　|1/2
--------------------------------------------
3　　　　|3　　　|3　　　　|1/3
--------------------------------------------
3　　　　|4　　　|4　　　　|1/4
--------------------------------------------
3　　　　|5　　　|4　　　　|1/5
--------------------------------------------
3　　　　|6　　　|4　　　　|1/6
--------------------------------------------
3　　　　|7　　　|2　　　　|1/7
--------------------------------------------

これら三つのテーブルを連結し、尚且つユーザ定義関数を使用し、下記のような結果を得たいです。
--------------------------------------------------
会員CD　|会員名　|魚類　|野菜　|牛肉　|豚肉　|
--------------------------------------------------
1　　　 |田中　　|1　　 |3　　 |1　　 |2　　 |
--------------------------------------------------
2　　　 |佐藤　　|4　　 |1　　 |0　　 |2　　 |
--------------------------------------------------
3　　　 |中井　　|0　　 |1　　 |3　　 |3　　 |
--------------------------------------------------

要は、親テーブルの会員をベースに、子テーブルに登録されている情報を分類CD単位で集計し、横に展開し、１レコードで保持したいと思っています。

わかりにくい文とは思いますが、どうか教えていただけますようよろしくお願い致します。

回答No.3

jamshid6 · ベストアンサー

#1,#2です。補足です。

とりあえずお役に立てたなら何よりですが、「テーブル関数でもいい」ということは「SQL Serverにオブジェクトを作成してもいい」ということですよね。
テーブル関数がOKで、ストアドプロシージャはNGという理由が、特になければ、ストアドプロシージャの利用も検討してみてください。

SQL Serverは「ストアドプロシージャ内でSELECTした結果がレコードセットで受け取れる」という比較的珍しいDBMSです。

回答No.2

jamshid6

#1です。
必要なSQL文は以下の通りです。
SUMの部分が分類コードの数だけ必要なので「組み上げる」必要があるわけですが。

SELECT p.会員CD,p.会員名,
SUM(CASE WHEN 分類CD=1 THEN 1 ELSE 0 END) [魚類],
SUM(CASE WHEN 分類CD=2 THEN 1 ELSE 0 END) [野菜],
SUM(CASE WHEN 分類CD=3 THEN 1 ELSE 0 END) [牛肉],
SUM(CASE WHEN 分類CD=4 THEN 1 ELSE 0 END) [豚肉]
FROM 親テーブル p
LEFT OUTER JOIN 子テーブル c ON c.会員CD=p.会員CD
GROUP BY p.会員CD,p.会員名
ORDER BY p.会員CD

呼び出す元はVBでもVBScriptでも何も変わらないのですが、前に書いた通り、
「戻りのカラム数が不定の場合、ユーザ定義関数では実現できません」。

ただ正直、使っている用語がかみ合っていないような気がしています。
ここで聞く以上、ユーザ定義関数って、SQL Serverのテーブル関数のことですよね？
まさかVBのユーザ定義関数じゃないですよね？

お礼

再度のご回答ありがとうございます。

ユーザ定義関数はSQL Serverのテーブル関数のことです。
ただ、一度しか作成した事がなかったもので、カラム数が不定の物は作成できないというのが理解できていませんでした。

上記で教えていただいたSQLのSUM部分をプログラム内で動的に作成し、教えていただいたSQLを元にがんばってみようと思います！

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

回答No.1

jamshid6

戻りのカラム数が不定のクエリはユーザ定義関数では実現できません。
が、以下の通り、ストアドプロシージャならば実現できます。
動的クエリを組み上げて実行します。

CREATE PROCEDURE GETMATRIX
AS
BEGIN
SET NOCOUNT ON

DECLARE @sql varchar(8000)
SELECT @sql=ISNULL(@sql+',','')
+'SUM(CASE WHEN 分類CD='+CONVERT(varchar,分類CD)+' THEN 1 ELSE 0 END) ['+分類名+']'
FROM 分類マスタ
ORDER BY 分類CD

SET @sql='SELECT p.会員CD,p.会員名,'+@sql
+' FROM 親テーブル p'
+' LEFT OUTER JOIN 子テーブル c ON c.会員CD=p.会員CD'
+' GROUP BY p.会員CD,p.会員名'
+' ORDER BY p.会員CD'
EXEC (@sql)
END

VBからADOで呼ぶなら、ADODB.CommandのCommandTextに"EXEC GETMATRIX"をセットするか、
CommandTypeにadCmdStoredProcを指定した上で、CommandTextに"GETMATRIX"とセットするかしてください。

もう一つの留意点は、中でsql文を組みあげていますが、この組み上げたSQL文が8000バイトを超えると
エラーになります（分類マスタの項目数によります）。その場合は、少し工夫が必要になります。

お礼

完全なこちらの記入ミスなのですが、vbScriptを使用して、ASPにて使用したいと思っています・・・

申し訳ありません。
質問ばかりで申し訳ないのですが、下記のようなことはできませんでしょうか？
子供のテーブルのみを対象として

--------------------------------------------------
会員ID|魚類　|野菜　|牛肉　|豚肉　|
--------------------------------------------------
1 　　|1　　 |3　　 |1　　 |2　　 |
--------------------------------------------------
2　　 |4　　 |1　　 |0　　 |2　　 |
--------------------------------------------------
3　　 |0　　 |1　　 |3　　 |3　　 |
--------------------------------------------------
のような結果をSQL文、もしくはユーザ定義関数を使用して実現することはできないでしょうか？