SQLがわかりません。その２

質問

VB2010とSQL Server2008 R2　でWindowsアプリを作っている新米です。

下のような表（DataDridView）で表示したいと考えています。（本当は月曜から金曜までの
５日間ですが、便宜上４日間で表現しました。）

品名ID　月曜　入荷　出荷　火曜　入荷　出荷　水曜　入荷　出荷　木曜　入荷　出荷

aaaaaa　6/12　2000　1500　6/13　1000　1000　6/14　1300　2000　6/15　3300　2000
bbbbbb　6/12　1000　2500　6/13　3000　4500　6/14　4200　3000　6/15　2000　1000
cccccc　6/12　1000　1500　6/13　1000　3400　6/14　1700　2600　6/15　3500　1000
dddddd　6/12　1500　1800　6/13　2200　1000　6/14　3300　1000　6/15　4100　2200
　・
　・

テーブル(table_meisai)はこのように構成されています。
meisai_id
hinmei_id
date
in_suryo
out_suryo

したがって
SELECT hinmei_id,date,in_suryo,out_suryo
FROM table_meisai
WHERE date BETWEEN 6/12 AND 6/16

とすると、当然のことながら

aaaaaa　6/12　2000　1500
aaaaaa　6/13　1000　1000
aaaaaa　6/14　1300　2000
aaaaaa　6/15　3300　2000
aaaaaa　6/16　3200　3500
bbbbbb　6/12　1000　2500
bbbbbb　6/13　3000　4500
　・
　・
と出てきてしまうわけです。

で、これを上記のような「hinmei_id」単位で１行にした「表」にしたいと考えているのですが、
そのSQLがわからないという状況です。

SQLの組立て方法を教えて下さい。

よろしくお願いします。

回答No.4

3rd_001

補足

週の数え方については以下を参照。
http://ja.wikipedia.org/wiki/%E6%97%A5%E4%BB%98
http://msdn.microsoft.com/ja-jp/library/ms174420.aspx

製造系だと週の考え方がやっかいです。
多分、指摘されます。

回答No.3

3rd_001

参考までに。

create table table_meisai
(
meisai_id int,
hinmei_id varchar(10)
date datetime,
in_suryo int,
out_suryo int
);

insert into table_meisai values(1,'aaaaaa','2011/6/12',2000,1500);
insert into table_meisai values(2,'aaaaaa','2011/6/13',1000,1000);
insert into table_meisai values(3,'aaaaaa','2011/6/14',1300,2000);
insert into table_meisai values(4,'aaaaaa','2011/6/15',3300,2000);
insert into table_meisai values(5,'aaaaaa','2011/6/16',3200,3500);
insert into table_meisai values(6,'bbbbbb','2011/6/12',1000,2500);
insert into table_meisai values(7,'bbbbbb','2011/6/13',3000,4500);

明細行をCASE文で横展開して段々のデータに変換、
段々になっているのをキー項目で集計することで1行に集計する方法。

(1)各日付の曜日を求める。DATEPART(w, date)
(2)求めた曜日をCASE文で判定し、各曜日に振り分ける。
(3)集計する際、週をわけないとNGなので週数も求める。 DATEPART(ww, date)
(4)品名IDと週数をキーにして集計する。
各曜日の日付は週数をキーにしているので一意になるはずなので、maxを指定する。minでも可。単に集計関数を指定しないとエラーになるから指定しているだけです。
同じく、各週の入庫・出庫も集計関数を指定しないとエラーになるのでsumをかける。

以下は推測です。
※横の数を決める必要があるので土日も追加した。
※そもそも、週が異なる場合、2行にならないとおかしいので週数を追加した。
※6月12日は日曜日。

select
hinmei_id,
DATEPART(ww, date) as 週,
--
max(case when DATEPART(w, date) = 1 THEN date else null end ) as 日曜日,
sum(case when DATEPART(w, date) = 1 THEN in_suryo else null end ) as 日曜_入庫,
sum(case when DATEPART(w, date) = 1 THEN out_suryo else null end ) as 日曜_出庫,
--
max(case when DATEPART(w, date) = 2 THEN date else null end ) as 月曜日,
sum(case when DATEPART(w, date) = 2 THEN in_suryo else null end ) as 月曜_入庫,
sum(case when DATEPART(w, date) = 2 THEN out_suryo else null end ) as 月曜_出庫,
--
max(case when DATEPART(w, date) = 3 THEN date else null end ) as 火曜日,
sum(case when DATEPART(w, date) = 3 THEN in_suryo else null end ) as 火曜_入庫,
sum(case when DATEPART(w, date) = 3 THEN out_suryo else null end ) as 火曜_出庫,
--
max(case when DATEPART(w, date) = 4 THEN date else null end ) as 水曜日,
sum(case when DATEPART(w, date) = 4 THEN in_suryo else null end ) as 水曜_入庫,
sum(case when DATEPART(w, date) = 4 THEN out_suryo else null end ) as 水曜_出庫,
--
max(case when DATEPART(w, date) = 5 THEN date else null end ) as 木曜日,
sum(case when DATEPART(w, date) = 5 THEN in_suryo else null end ) as 木曜_入庫,
sum(case when DATEPART(w, date) = 5 THEN out_suryo else null end ) as 木曜_出庫,
--
max(case when DATEPART(w, date) = 6 THEN date else null end ) as 金曜日,
sum(case when DATEPART(w, date) = 6 THEN in_suryo else null end ) as 金曜_入庫,
sum(case when DATEPART(w, date) = 6 THEN out_suryo else null end ) as 金曜_出庫,
--
max(case when DATEPART(w, date) = 7 THEN date else null end ) as 土曜日,
sum(case when DATEPART(w, date) = 7 THEN in_suryo else null end ) as 土曜_入庫,
sum(case when DATEPART(w, date) = 7 THEN out_suryo else null end ) as 土曜_出庫
from table_meisai
group by hinmei_id,DATEPART(ww, date)

考え方は示したので参考にしてください。

回答No.2

t-ka

昨日の者です。ごめんなさい　AS400VBNET　さんのコードで正解です。
歳かな？。4X
MAXの説明？　スカラ値にすること。 　

私のでは
aaaaaa　6/12　2000　1500
aaaaaa　6/13　1000　1000
aaaaaa　6/14　1300　2000
aaaaaa　6/15　3300　2000
aaaaaa　6/16　3200　3500
bbbbbb　6/12　1000　2500
bbbbbb　6/13　3000　4500
にもう一つ
aaaaaa 6/12 999 499
なんてデータがあったら破たんします。

回答No.1

AS400VBNET

出力形式に問題があると思われますが、
下記内容で表示は可能と思います。（未確認）

SELECT hinmei_id, '6/12' AS 月曜,
SUM(CASE WHEN date = '2011/06/12 00:00:00' THEN in_suryo ELSE 0 END)
AS 入荷,
SUM(CASE WHEN date = '2011/06/12 00:00:00' THEN out_suryo ELSE 0 END)
AS 出荷, '6/13' AS 火曜,
SUM(CASE WHEN date = '2011/06/12 00:00:00' THEN in_suryo ELSE 0 END)
AS 入荷,
SUM(CASE WHEN date = '2011/06/12 00:00:00' THEN out_suryo ELSE 0 END)
AS 出荷
FROM Table
GROUP BY hinmei_id

日付に関してはSQL文を発行するときに動的に変更する必要があります。