集計するためのSQL構文を教えて下さい。

質問

初めて投稿します。よろしくお願いします。
現在、ストアドプロシージャとSQLの勉強をしながら作業を行なっています。そのため説明不足などありましたらご指摘願います。
[環境]
　データベース：SQLServer2005
　SQL作成環境 ：SQLServerManagementStudioExpress(9.00.2047.00)

望んでいる出力結果
病室，タイプ，10／01水，10／02木，10／03金，10／04土，10／05日
--------------------------------------------------------------
１０，集合３，○○太郎，○○太郎，○○太郎，○○太郎，□□花子
　　　　　　　△△太郎，△△太郎，□□花子，□□花子
　　　　　　　□□花子，□□花子
１１，個室Ａ，　　　　　　　　　　××太郎，××太郎，××太郎
１２，個室Ｂ，　　　　　○○次郎，○○次郎，○○次郎，○○次郎

※指定した日付範囲で、全病室を対象に入院中患者、入院予定患者を
　病室単位で求めたいです。

関係するテーブル情報
※マスタテーブルは、名称+Mで表記します。
※データテーブルは、名称+Tで表記します。
[病室]
病室M(
　病室番号 INT
　タイプコード INT
)
[日付]
ストアドのパラメータで集計開始日と終了日を受け取るため、
その集計範囲も動的に変わります。
※上記の結果では、
　集計日（自）：2008/10/01
　集計日（至）：2008/10/05
を受け取った場合を想定し記述しています。

[入院者氏名]
患者T(
　患者コード　INT
　患者氏名　VARCHAR(128)
　通院種別　TINYINT(1:通院,2:入院)
　退院区分　TINYINT(1:入院,2:退院)
　入院日　SMALLDATETIME
　退院日　SMALLDATETIME
)

[患者と病室を紐付けるテーブル]
病室割当T(
　割当番号　INT
　患者番号　INT
　病室番号 INT
)

長文となり申し訳ありません。
上記の情報でストアドを組もうとしているのですが、
SQL含め勉強不足な状態です。
実現するための考え方、方法、アドバイス等を頂きたいです。
よろしくお願い致します。

回答No.1

jamshid6 · ベストアンサー

これは実行結果はExcelやReporting Serviceに流し込むという理解でいいのですよね。
私は毎日のようにこんなのを書いているのですが、いろいろなテクニックが必要なので、少しずつ理解してください。

・日付テーブルを先に作って、入院期間のデータに増幅します。
・戻りの列数が固定でないクエリは動的クエリを使って組み立てます。
　SQL Server 2005ではvarchar(max)を使います。
　動的クエリを使うとクエリが見にくくなるので、必要なデータを一旦テーブルに収録します。
　この場合はテーブル変数が使えないため、一時テーブルを利用します。
・動的クエリにはsp_executesqlを使う方法もあり、パフォーマンスを考慮すればそちらの方がいいのですが、
　組み立てがやや面倒なので、EXECを使っています。
（最後にどんなクエリが組み立てられているかは、EXEC(@sql)をprint @sqlに置き換えれば見られます）
・日本語の曜日を求める関数はないので、以下のようにしましたが、利用頻度が高ければスカラー関数を作るのも手です。
-------------------------------------------------------------------------------
CREATE PROCEDURE ap_病室割当一覧(@DATEF smalldatetime,@DATET smalldatetime)
AS
BEGIN
SET NOCOUNT ON

DECLARE @WDATE smalldatetime
DECLARE @WEEKSTR varchar(20)
DECLARE @DATETBL TABLE ([日付][smalldatetime],[曜日][varchar](5))
SET @WEEKSTR = '日月火水木金土'

--指定期間の日付テーブルを作成
SET @WDATE=@DATEF
WHILE (@WDATE<=@DATET)
BEGIN
INSERT INTO @DATETBL VALUES
(@WDATE,SUBSTRING(@WEEKSTR,DATEPART(dw,@WDATE),1))
SET @WDATE=DATEADD(d,1,@WDATE)
END

--動的クエリ用にデータを一旦蓄積
CREATE TABLE #MAPDATA
([病室番号][int],[タイプ][varchar](100),[日付][smalldatetime],[患者氏名][varchar](128),[SEQ][int])

INSERT INTO #MAPDATA
SELECT bw.病室番号,bt.タイプ,dt.日付,k.患者氏名,
ROW_NUMBER() OVER (PARTITION BY bw.病室番号,dt.日付 ORDER BY bw.患者番号) SEQ
FROM 病室割当T bw
INNER JOIN 病室M b ON b.病室番号=bw.病室番号
INNER JOIN 病室タイプM bt ON bt.タイプコード=b.タイプコード
INNER JOIN 患者T k ON k.患者コード=bw.患者番号
INNER JOIN @DATETBL dt ON dt.日付 BETWEEN k.入院日 AND DATEADD(d,-1,k.退院日)

--動的クエリを構築して実行
DECLARE @sql varchar(max)
SET @sql = 'SELECT 病室番号,タイプ'
SELECT @sql=@sql
+',MAX(CASE WHEN 日付='''+CONVERT(varchar,日付,111)+''' THEN 患者氏名 END)'
+' ['+RIGHT(CONVERT(varchar,日付,111),5)+曜日+']'
FROM @DATETBL
ORDER BY 日付
SET @sql=@sql+' FROM #MAPDATA'
+' GROUP BY 病室番号,タイプ,SEQ'
+' ORDER BY 病室番号,タイプ,SEQ'

EXEC (@sql)

DROP TABLE #MAPDATA
END

お礼

返信が遅くなりすいませんでした。
jamshid6さん、解り易いご提示をありがとうございます。
>これは実行結果はExcelやReporting Serviceに流し込むという理解でいいのですよね。
情報提供が少なくすいませんでした。
ストアドプロシージャでの集計結果をActiveReportsへバインドします。
jamshid6さんのご説明で集計ロジックを組む形式（流れ）が初めてながら見えた気がします（6割ほどですが…）
私の方での実行結果は、期待する結果を得ることが出来ましたが、
jamshid6さんからご提示頂いたソースが理解できるよう勉強します。
ご丁寧なアドバイスありがとうございました。
また質問することがあるかと思いますが、今後ともよろしくお願い致します。

回答No.2

jamshid6

#1です。
書き忘れましたが、病室タイプを管理するマスタがなかったので、勝手に想定して足しています。
病室タイプM(
タイプコード INT,
タイプ INT
)