- PR -

第一正規形→非正規形への非正規化方法

1
投稿者投稿内容
sophia(rc)
会議室デビュー日: 2005/09/02
投稿数: 18
投稿日時: 2006-06-15 11:09
　Access2000/2003(Jet4.0) や SQL Server2000 で以下のようなテーブルがあった場合、
table_name
id,name
1,たぬ貴
2,きつ音

table_favorite
id,fav
1,葉っぱ
2,箱
2,尻尾

第一正規形までは SQL で戻せますが、これを非正規形の
table_list
id,name,fav
1,たぬ貴,葉っぱ
2,きつ音,箱/尻尾

のような形に戻すとき、現在は table_list の fav が空欄の結果を用意しておき、
table_favorite の Recordset を作って、ループをまわして table_list の fav を
埋めていますが、他にすっきりした方法がありますでしょうか。
　cat(fav, '/') などと指定したら上記のようになる集計関数があれば
大変 Cooooool だと考えているのですが、残念ながらみつかりませんでした…。
今川 美保(夏椰)
ぬし
会議室デビュー日: 2004/06/10
投稿数: 363
お住まい・勤務地: 神奈川県茅ヶ崎市
投稿日時: 2006-06-15 11:40
引用:

sophia(rc)さんの書き込み (2006-06-15 11:09) より:

上記のようになる集計関数があれば
大変 Cooooool だと考えているのですが、残念ながらみつかりませんでした…。

SQLServerの既存集計関数では存在しないですね。

で、作ってみました。
#1つのテーブルにあるidごとにvalを連結し、
#その結果を返すテーブル値ファンクションです。

このファンクションを
ご自由にカスタマイズして定義し、
SQL文からJOINなどで使用して使ったら実現できると思います。

コード:
create function MargeTest2()
RETURNS @ret Table (
	id int,
    addval nvarchar(500)
)
AS
begin

	declare @id int ;
	declare @prev_id int ;
	declare @val nvarchar(100) ;
	declare @addval nvarchar(500) ;

	DECLARE cur_ref CURSOR FOR
		SELECT id, val
		FROM Test2
		ORDER BY id	

	OPEN cur_ref ;

	Set @prev_id = -1 ;

	FETCH NEXT FROM cur_ref
	INTO @id, @val ;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		if @prev_id <> @id 
		begin
			if @prev_id >= 0
			begin 
				insert into @ret values(@prev_id, @addval) ;
			end ;

			set @addval = @val ;
			set @prev_id = @id ;
		end ;
		else
			set @addval = @addval + @val ;
		
		FETCH NEXT FROM cur_ref
		INTO @id, @val ;
	END ;
	if @prev_id >= 0
	begin 
		insert into @ret values(@prev_id, @addval) ;
	end ;

	close cur_ref ;
	DEALLOCATE cur_ref ;

	RETURN 
end

_________________
夏椰 @ わんくま同盟
夏椰の庵
Microsoft MVP for Windows Server System - SQL Server ( Jul 2006 - Jun 2008 )
明智重蔵
大ベテラン
会議室デビュー日: 2005/09/05
投稿数: 127
投稿日時: 2006-06-15 11:59
SQLServerの親戚のSQL AnywhereだとListがありますけどね
SQLServer2000には残念ながらないです
未記入
ぬし
会議室デビュー日: 2004/09/17
投稿数: 667
投稿日時: 2006-06-15 17:18
SQL Server 2000 のサンプルデータベース Northwind のテーブル Products に対して
CategoryID ごとの ProductName を連結して結果を返す例です。

・select 句で 変数代入をおこなうと行ごとに代入が繰り返される。(これを利用して文字列連結をしています。)
・フェッチループが明細行数ではなくグループ化数で済む。
・フェッチ命令が一箇所だけに記述されている。

コード:
use Northwind
go

declare @ProductNames table (
	CategoryID int,
	ProductNames varchar(8000)
)

declare cur cursor for
	select distinct CategoryID
	from Products
	order by CategoryID

open cur

declare @CategoryID int
declare @Names varchar(8000)

while (0=0) begin
	fetch next from cur into @CategoryID
	if @@FETCH_STATUS <> 0 break

	set @Names = ''
	select @Names = @Names + '/' + ProductName
	from Products
	where CategoryID = @CategoryID
	order by CategoryID, ProductID

	insert into @ProductNames (CategoryID, ProductNames)
	values (@CategoryID, substring(@Names, 2, 8000))
end

close cur
deallocate cur

select * from @ProductNames
sophia(rc)
会議室デビュー日: 2005/09/02
投稿数: 18
投稿日時: 2006-07-09 11:32
　返答が遅くなってしまいすみませんでした。
　SQL Anywhereには、ズバリ目的に合ったCoooolな関数があるのですね。
　今回は非正規形のテーブルとして取り出して JOIN するような使い方はせず、多くとも数行程度必要なだけだったので、非正規形が欲しい列の数だけ ID とデリミタを受け取ってデリミタ区切りの値を返すスカラ値関数を作ってみました。
　これだと呼ばれる都度カーソルを開くため大変効率が悪そうなので、次回同じ事をする必要が出たときは未記入さんや夏椰さんのテーブルとして返すやり方も試してみたいと思います。ありがとうございました。
1

スキルアップ／キャリアアップ（JOB@IT）