SQL SERVER2005でシフト表を出力したい

質問

社員のシフト管理表を作成しています。
visual web developer2008とSQL Server2005にて社内で利用するWEBアプリを作成しています。
CSVにてエクスポートしたいという要件が出てきており、その抽出条件が私には難しくお知恵をお借りしたいと思い投稿いたしました。
予定テーブル(tbl_yotei)には日付と必要人数と場所が入っており、社員予定テーブル(tbl_shain_yotei)には予定IDと社員名が入っています。

■予定テーブル(tbl_yotei)
yotei_id　　jishi_day　　　ninzu　　　shiten
1　　　　　　2011/7/1　　　2　　　　　支店A
2　　　　　　2011/7/2　　　1　　　　　支店A
3　　　　　　2011/7/3　　　3　　　　　支店A
4　　　　　　2011/7/2　　　1　　　　　支店B
5　　　　　　2011/7/3　　　2　　　　　支店B
6　　　　　　2011/7/4　　　3　　　　　支店A
------

■社員予定テーブル(tbl_shain_yotei)
shain_yotei_id　　　yotei_id　　　shain_nam
1　　　　　　　　　　　1　　　　　　　　田中
2　　　　　　　　　　　1　　　　　　　　山根
3　　　　　　　　　　　2　　　　　　　　杉下
4　　　　　　　　　　　3　　　　　　　　田中
5　　　　　　　　　　　3　　　　　　　　一条
6　　　　　　　　　　　4　　　　　　　　魚島

これを予定テーブルの人数分の行を表示し、その行にアサインされている社員が表示される表を作成したくご質問いたしました。

まだアサインされていない予定については社員はnullで人数分の行を作成したいと思います。
入力時の制限として予定テーブルに入力されている「人数」よりも多い数の社員は割り当てていません。予定テーブルのある行の人数が2なら社員予定テーブルの行数は0～2行までです。

ストアドプロシージャで予定テーブルに入っている人数分の行を出力する事は出来たのですが、そこから社員予定テーブルを参照して下記のような表になるようにする方法がわかりません。

例えば
tbl_yoeti.yotei_idが3のレコードは3行表示し、shain_yotei_id.yotei_idが3のレコードは2行あるので2行はshain_namに社員の氏名が入り、1行はshain_namがnullのレコードを表示させたいです。

yotei_id　　jishi_day　　ninzu　　shiten　　shain_yotei_id　　shain_nam
1　　　　　　2011/7/1　　2　　　　支店A　　　　1　　　　　　　　田中
1　　　　　　2011/7/1　　2　　　　支店A　　　　2　　　　　　　　山根
2　　　　　　2011/7/2　　1　　　　支店A　　　　3　　　　　　　　杉下
3　　　　　　2011/7/3　　3　　　　支店A　　　　4　　　　　　　　田中
3　　　　　　2011/7/3　　3　　　　支店A　　　　5　　　　　　　　一条
3　　　　　　2011/7/3　　3　　　　支店A　　　　Null　　　　　　Null
4　　　　　　2011/7/4　　1　　　　支店B　　　　6　　　　　　　　魚島
5　　　　　　2011/7/5　　2　　　　支店B　　　　Null　　　　　　Null
5　　　　　　2011/7/5　　2　　　　支店B　　　　Null　　　　　　Null
6　　　　　　2011/7/6　　3　　　　支店A　　　　Null　　　　　　Null
6　　　　　　2011/7/6　　3　　　　支店A　　　　Null　　　　　　Null
6　　　　　　2011/7/6　　3　　　　支店A　　　　Null　　　　　　Null
----

出来ているストアドプロシージャ
CREATE PROCEDURE dbo.StoredProcedure2
DECLARE @yotei_id int
DECLARE @jishiday datetime
DECLARE @ninzu int
DECLARE @shiten nvarchar(50)

DECLARE c_yotei cursor FOR
SELECT yotei_id,ninzu,jishiday,shiten FROM tbl_yotei

CREATE TABLE #TMP
(
yotei_id int,ninzu int, jishiday datetime, shiten nvarchar(50)
)

OPEN c_yotei

FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu, @jishiday, @shiten
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @kaisu int
SET @kaisu = 1
WHILE @kaisu <= @ninzu
BEGIN
INSERT INTO #TMP (
yotei_id,ninzu,jishiday,shiten
) VALUES (
@yotei_id,@ninzu,@jishiday,@shiten
)
SET @kaisu = @kaisu + 1
END
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu, @jishiday, @shiten
END

CLOSE c_yotei
DEALLOCATE c_yotei

/*
ここからどのようにしたらよいでしょうか。。
*/

SELECT * FROM #TMP

RETURN
----
上記のストアドからでも全く新しいアイデアでもかまいませんので皆様お知恵をお貸しください。
どうかよろしくお願いいたします。

回答No.2

Siegrune · ベストアンサー

あ、ごめんなさい。
ANo1のものです。
ミスしてます。

ANo1では、
yotei_id　　jishi_day　　ninzu　　shiten　　shain_yotei_id　　shain_nam
5　　　　　　2011/7/5　　3　　　　支店B　　　　Null　　　　　　Null
ができてしまいます。

>insert into #TMP
>select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,b.shain_yotei_id,b.shain_nam
>from tbl_yotei a,tbl_shain_yotei b where a.yotei_id = b.yotei_id
>
>を実行する。（shain_yotei_idとshain_namがNULLでないレコードが格納される）
まではいいのですが、

>select max(ninzu) from tbl_yotei
>の値を取得する。
ここは、
select yotei_id,ninzu from tbl_yotei
をカーソルで取得して、１件ずつ処理する必要があります。

>
>while等を使って1から上で取得したmax(ninzu)まで以下のＳＱＬを繰り返す。
>insert into #TMP
>select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,NULL,NULL
>from tbl_yotei a
>where (select count(*) from #temp b where b.yotei_id = a.yotei_id) < 繰り返しの値
これは、上で取得したレコード毎に繰り返し処理します。

ということで、
(元質問に書かれているのと同様に)
DECLARE c_yotei cursor FOR
SELECT yotei_id,ninzu FROM tbl_yotei

OPEN c_yotei
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu
WHILE @@FETCH_STATUS = 0
BEGIN
set @i = 1
while @i > @ninzu
insert into #TMP
select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,NULL,NULL
from tbl_yotei a
where a.yotei_id = @wk_yotei_ID and
(select count(*) from #temp b where b.yotei_id = @wk_yotei_ID) < @i
set @i = @i + 1
end
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu
end

ということで。
（すいませんでした。）

お礼

Siegrune様
再度投稿ありがとうございます。
＞すいませんでした。
とんでもないです。こちらこそ何度もお答えいただいてありがとうございます。
実は先ほど一度教えていただいたストアドを書いてみて今回ご指摘の結果になる事にきづき、改めて考えていた所でございました！
各レコードのninzuの数だけ調べるという事ですね。ありがとうございます。
しかし、今回の大きなヒントになったのはNULLを後にインサートするという所がポイントとなり前に進めました！
ありがとうございます。

補足

Siegrune様

できました！！ありがとうございました！！
下記ストアドプロシージャで思った結果が出てきました。

---
ALTER PROCEDURE dbo.StoredProcedure1 AS

DECLARE @yotei_id INT
DECLARE @ninzu int

--一時テーブル
CREATE TABLE #TMP
(
yotei_id INT,
jishi_day DATETIME,
ninzu INT,
shiten NVARCHAR(50),
shain_yotei_id INT,
shain_nam NVARCHAR(50)
)

INSERT INTO #TMP
SELECT
a.yotei_id,
a.jishi_day,
a.ninzu,
a.shiten,
b.shain_yotei_id,
b.shain_nam
FROM
tbl_yotei a,
tbl_shain_yotei b
WHERE a.yotei_id = b.yotei_id

DECLARE c_yotei CURSOR FOR
SELECT yotei_id,ninzu FROM tbl_yotei
OPEN c_yotei
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @i INT
SET @i = 0
WHILE @i < @ninzu
BEGIN
INSERT INTO #TMP
SELECT
a.yotei_id,
a.jishi_day,
a.ninzu,
a.shiten,
NULL,
NULL
FROM tbl_yotei a
WHERE a.yotei_id = @yotei_id AND
(SELECT COUNT(*) FROM #TMP b WHERE b.yotei_id = @yotei_id) <= @i
SET @i = @i + 1
END
FETCH NEXT FROM c_yotei INTO @yotei_id, @ninzu
END

SELECT * FROM #TMP ORDER BY yotei_id

RETURN

---
【結果】
yotei_id　　　jishi_day　　　ninzu　　　shiten　　　shain_yotei_id　　　shain_nam
1　　　　　2011/07/01 0:00:00　　2　　　　支店A　　　　　　1　　　　　　　田中
1　　　　　2011/07/01 0:00:00　　2　　　　支店A　　　　　　2　　　　　　　山根
2　　　　　2011/07/02 0:00:00　　1　　　　支店A　　　　　　3　　　　　　　杉下
3　　　　　2011/07/03 0:00:00　　3　　　　支店A　　　　　　4　　　　　　　田中
3　　　　　2011/07/03 0:00:00　　3　　　　支店A　　　　　　5　　　　　　　一条
3　　　　　2011/07/03 0:00:00　　3　　　　支店A
4　　　　　2011/07/02 0:00:00　　1　　　　支店B　　　　　　6　　　　　　　魚島
5　　　　　2011/07/03 0:00:00　　2　　　　支店B
5　　　　　2011/07/03 0:00:00　　2　　　　支店B
6　　　　　2011/07/04 0:00:00　3　　　　支店A
6　　　　　2011/07/04 0:00:00　3　　　　支店A
6　　　　　2011/07/04 0:00:00　3　　　　支店A
---

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

回答No.1

Siegrune

## 単に外部結合するだけで実現できると思いきや、甘かった。

方法案
#TMPは、yotei_id,jishi_day,ninzu,shiten,shain_yotei_id,shain_nam
にする。

insert into #TMP
select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,b.shain_yotei_id,b.shain_nam
from tbl_yotei a,tbl_shain_yotei b where a.yotei_id = b.yotei_id

を実行する。（shain_yotei_idとshain_namがNULLでないレコードが格納される）

select max(ninzu) from tbl_yotei
の値を取得する。

while等を使って1から上で取得したmax(ninzu)まで以下のＳＱＬを繰り返す。
insert into #TMP
select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,NULL,NULL
from tbl_yotei a
where (select count(*) from #temp b where b.yotei_id = a.yotei_id) < 繰り返しの値

例示されたものでは、繰り返しの値は、1から3まで処理されます。
yotei_id=3の部分だけでいうと、count(*)は2。
繰り返しの値が1のときは、
(select count(*) from #temp b where b.yotei_id = a.yotei_id) < 1
何もしない。
繰り返しの値が2のときは、
(select count(*) from #temp b where b.yotei_id = a.yotei_id) < 2
何もしない。
繰り返しの値が3のときは、
(select count(*) from #temp b where b.yotei_id = a.yotei_id) < 3　は成立。
insert into #TMP
select a.yotei_id,a.jishi_day,a.ninzu,a.shiten,NULL,NULL
from tbl_yotei a
が実行される。
といった具合です。

お礼

Siegrune様
投稿ありがとうございます。
そうなんです。聞いた感じだとまあできそうな気がしていたのですが、いざやってみるととても大変でびっくりしています。

データのある行を先に#TMPに入れてしまってその後nullだけという二段階のインサート！私はぜんぜん思いつきませんでした！
いただいたアイデアを元に作成してみます。
max(ninzu)の結果を変数に入れる方法などまだわかっていないので調べて組み立ててみます。

本当にありがとうございます！

他のアイデアがありましたらどうぞ投稿お願いいたします。