ユーザー定義関数作成について教えてください。

質問

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

[MST_table_A] (都道府県マスター)
_______________________
都道府県CD　| 都道府県名
-----------------------
　　　　　1 | 大阪
-----------------------
　　　　　2 | 名古屋
-----------------------
　　　　　3 | 北海道
-----------------------

[TRN_table_B] (名物テーブル)
_________________________________
都道府県CD | 名物CD | 名物名
---------------------------------
　　　　1　|　　　1 | たこ焼き
---------------------------------
　　　　1　|　　　2 | お好み焼き
---------------------------------
　　　　1　|　　　3 | 豚まん
---------------------------------
　　　　2　|　　　1 | ういろう
---------------------------------
　　　　2　|　　　2 | きしめん
---------------------------------
　　　　3　|　　　1 | ホタテ
---------------------------------
　　　　3　|　　　2 | カニ
---------------------------------
　　　　3　|　　　3 | じゃがいも
---------------------------------

質問なのですが、例えば上記のような親子関係のテーブル
[MST_table_A] (都道府県マスター)
[TRN_table_B] (名物テーブル)より、下記の様なマスター１レコードに対し、
子供の情報を１フィールド(Tab区切り)で取得する為には
どのようなユーザー関数を作成し、どのように実行すれば実現することができるのでしょうか？

[処理結果]
__________________________________________________________________
都道府県CD |　都道府県名 | ★名物名全データ★
------------------------------------------------------------------
　　　　1　|　　　　大阪 | たこ焼き vbTab お好み焼き vbTab 豚まん
------------------------------------------------------------------
　　　　1　|　　　名古屋 | ういろう vbTab きしめん
------------------------------------------------------------------
　　　　1　|　　　北海道 | ホタテ vbTab カニ vbTab じゃがいも
------------------------------------------------------------------

回答No.3

jamshid6 · ベストアンサー

スカラー関数自体はパラメータを受け取って、スカラー（今回の場合はvarchar文字列）を返すだけですから説明は不要として、おそらくわかりにくいのは
DECLARE @STR varchar(8000)
SELECT @STR=ISNULL(@STR+@DELIM,'')+[名物名] FROM TRN_table_B
WHERE [都道府県CD]=@PREFCD
ですよね？

もともとテーブルの値を変数に受けるときに、
SELECT @STR=[名物名] FROM TRN_table_B WHERE ...
という書き方をします。
(SET @STR = (SELECT [名物名] FROM TRN_table_B WHERE ...)でもいいですが、MSの今の推奨は上）

このとき該当するデータが１件だけならわかりやすいですが、２件以上ある場合、SQL Serverでは検索される順番に「それぞれの行で」@STR=[名物名]が行われます。
例えば上記SELECTの該当が３件で、それぞれ「たこ焼き」、「お好み焼き」、「豚まん」だとしたら、１行読み込むごとに@STRに「たこ焼き」=>「お好み焼き」=>「豚まん」とセットされていき、最後が「豚まん」なので、実行後の@STRは「豚まん」になります。

この性質を利用すると
DECLARE @STR varchar(8000)
SET @STR=''
SELECT @STR=@STR+[名物名] FROM TRN_table_B WHERE ...
という書き方ができます。
この書き方をすれば、１行読み込むごとにその行の名物名が足されていくことになるので、実行後の@STRには「たこ焼きお好み焼き豚まん」と入ります。

当然「区切り文字を入れたい」と思いますよね？
そこで
DECLARE @STR varchar(8000)
SET @STR=''
SELECT @STR=@STR+','+[名物名] FROM TRN_table_B WHERE ...
と書きなおすと、@STRの結果は「,たこ焼き,お好み焼き,豚まん」となります。
区切り文字は入りましたが、先頭にも区切り文字が入ってしまいます。

SUBSTRINGなどを使って先頭の区切り文字を削ってもいいわけですが、ここでもう１つの性質を利用します。
「SQL Serverでは文字列結合の中にNULLが入っていると結合結果もNULLになる」

上の例で「SET @STR=''」と初期値にブランクを指定している理由がここにあります。
（初期値設定がないと@STRはNULLですから、クエリの結果もNULLになってしまいます）
逆にいえば、NULLのときには何を結合しても結果はNULLです。

それで、
DECLARE @STR varchar(8000)
SELECT @STR=ISNULL(@STR+',','')+[名物名] FROM TRN_table_B WHERE ...
と書けるわけです。

１行目　名物名＝たこ焼き
@STRはNULLなので、@STR=ISNULL(@STR+',','')+[名物名]の部分は
=> @STR=ISNULL(NULL+',','')+'たこ焼き'
=> @STR=ISNULL(NULL,'')+'たこ焼き'
=> @STR=''+'たこ焼き'
=> @STR='たこ焼き'
２行目　名物名＝お好み焼き
@STRは'たこ焼き'なので、@STR=ISNULL(@STR+',','')+[名物名]の部分は
=> @STR=ISNULL('たこ焼き'+',','')+'お好み焼き'
=> @STR=ISNULL('たこ焼き,','')+'お好み焼き'
=> @STR='たこ焼き,'+'お好み焼き'
=> @STR='たこ焼き,お好み焼き'
以下同じ。
結果、先頭の区切り文字はなく、きれいに想定した結果になるというわけです。

仕組みを理解すると、応用が効きます。文字列加工のかなりのケースでカーソルが不要になります。
ぜひ活用してみてください。

補足

vb, SQLサーバーで開発をしてきましたが、恥ずかしながらユーザー定義関数と言うものをまだ作成したことがありませんでした。

そんな自分でもこれだけの文で理解することができました！
自分でもこれくらいの説明が人にできるようにがんばっていこうと思います。

最初から最後まで丁寧な解説、本当にありがとうございました。

回答No.2

jamshid6

改行はCHAR(13)+CHAR(10)です。
デリミタもパラメータにしておいたのですが、改行は２バイト必要なので、@DELIMの定義を少し変更します。

ALTER FUNCTION GETSTR(@PREFCD int,@DELIM varchar(2))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @STR varchar(8000)
SELECT @STR=ISNULL(@STR+@DELIM,'')+名物名 FROM TRN_table_B
WHERE 都道府県CD=@PREFCD
RETURN @STR
END

SELECT 都道府県CD,都道府県名,dbo.GETSTR(都道府県CD,CHAR(13)+CHAR(10)) FROM MST_table_A

補足

昨日まるまるかかりましたが、なんとか仕上げることができました！
本当にありがとうございました。

教えて頂いた関数を調べて考えてみたのですが、正直どういった動作の結果今回の結果を得ることができたのかわかりません。。。。

もしまだ観覧していただけていましたら、解説していただけませんでしょうか？
どうぞよろしくお願いします。

回答No.1

jamshid6

SQL Server 2000であれば、以下のようにするしかないと思います。
（2005だと、CLR集計関数がまさに目的の処理にはうってつけなのですが）
・１行１クエリを実行することになるので、レスポンスはいいとは言えません。
・8000バイトが限界なので、長すぎると切れます。

CREATE FUNCTION GETSTR(@PREFCD int,@DELIM char(1))
RETURNS varchar(8000)
AS
BEGIN
DECLARE @STR varchar(8000)
SELECT @STR=ISNULL(@STR+@DELIM,'')+[名物名] FROM TRN_table_B
WHERE [都道府県CD]=@PREFCD
RETURN @STR
END

SELECT 都道府県CD,都道府県名,dbo.GETSTR(都道府県CD,CHAR(9)) FROM MST_table_A

補足

求めていた結果がそのまま返ってきました！
すごく助かりました！

本当にありがとうございます。
追加で一点質問なのですが、上記[ tab ]を改行にしたいのですが、うまくいきません。。。

何か方法ありますでしょうか？