调试数据如下(for SQL2005+)：
--预订表(编号，产品型号，预订日期，预订数量)
CREATE TABLE Forecast(ID varchar(25), PartNumber varchar(25), BOM varchar(25), BeginDate smalldatetime, QtyCurrent decimal(14,4));
INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-7-1',0);
INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-8-1',1000);
INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-9-1',2000);
INSERT INTO Forecast VALUES('FO01','Part01','BOM01','2014-10-1',3500);
INSERT INTO Forecast VALUES('FO01','Part02','BOM02','2014-7-1',1200);
INSERT INTO Forecast VALUES('FO01','Part02','BOM02','2014-8-1',1800);
INSERT INTO Forecast VALUES('FO01','Part02','BOM02','2014-9-1',2500);
INSERT INTO Forecast VALUES('FO01','Part02','BOM02','2014-10-1',0);
GO
--预订区间数据表(编号，预订日期，显示标题)
CREATE TABLE ForecastOrderArea(ID varchar(25), BeginDate smalldatetime, Caption varchar(20));
INSERT INTO ForecastOrderArea VALUES('FO01','2014-7-1','JAL');
INSERT INTO ForecastOrderArea VALUES('FO01','2014-8-1','AUG');
INSERT INTO ForecastOrderArea VALUES('FO01','2014-9-1','SEP');
INSERT INTO ForecastOrderArea VALUES('FO01','2014-10-1','OCT');
INSERT INTO ForecastOrderArea VALUES('FO02','2014-7-1','JAL');
INSERT INTO ForecastOrderArea VALUES('FO02','2014-8-1','AUG');
INSERT INTO ForecastOrderArea VALUES('FO02','2014-9-1','SEP');
INSERT INTO ForecastOrderArea VALUES('FO02','2014-10-1','OCT');
GO
--预订BOM(编号，产品型号)
CREATE TABLE PartBOMMaster(ID varchar(25), PartNumber varchar(25));
INSERT INTO PartBOMMaster VALUES('BOM01','Part01');
INSERT INTO PartBOMMaster VALUES('BOM02','Part02');
GO
--预订BOM明细(编号，元件编码,数量)
CREATE TABLE PartBOMDetail(ID varchar(25), ComponentId varchar(25), Qty decimal(6,3));
INSERT INTO PartBOMDetail VALUES('BOM01','CAP01',3);
INSERT INTO PartBOMDetail VALUES('BOM01','CAP02',1);
INSERT INTO PartBOMDetail VALUES('BOM01','RES01',2);
INSERT INTO PartBOMDetail VALUES('BOM01','RES02',1);
INSERT INTO PartBOMDetail VALUES('BOM02','DIO01',3);
INSERT INTO PartBOMDetail VALUES('BOM02','CAP02',1);
INSERT INTO PartBOMDetail VALUES('BOM02','RES02',1);
INSERT INTO PartBOMDetail VALUES('BOM02','CAP01',1);
GO
需要统计出以下主从数据表：
--1. Master
ID PartNumber BOM JAL AUG SEP OCT
FO01 Part01 BOM01 0 3000 6000 3500
FO02 Part02 BOM02 1200 1800 2500 0
--2. Detail(单个)
ID Component JAL AUG SEP OCT
FO01 CAP01 0 3000 6000 10500
FO01 CAP02 0 1000 2000 3500
FO01 RES01 0 2400 4000 7000
FO01 RES02 0 1000 2000 3500
FO02 DIO01 3600 5400 7500 0
FO02 CAP01 1200 1800 2500 0
FO02 CAP02 1200 1800 2500 0
FO02 RES02 1200 1800 2500 0
--3. Detail(多个统计: FO01, FO02)
S_Id Component JAL AUG SEP OCT
1 CAP01 1200 4800 8500 10500
2 CAP02 1200 2800 4500 3500
3 DIO01 3600 5400 7500 0
4 RES01 0 2400 4000 7000
5 RES02 1200 2800 4500 3500
难点：
需按ForecastOrderArea的Caption 将Forecast表中的QtyCurrent横向显示出来；
BOM数据也需要向每月的需求数量横向显示出来(PartBOMDetail.Qty * Forecast.QtyCurrent)
------解决方案--------------------
第一个和第二个都是多个表链接以后的行列转换，精华帖中很多
------解决方案--------------------
请问Forecast表中为何木有ID=FO02的记录?
不明白如何得出master表的喔..
------解决方案--------------------
先顶一下，现在有点忙，等中午了，回来看看。
------解决方案--------------------
请问master表中,JAL=0,AUG=3000,SEP=6000,OCT=3500是如何计算出来的?
ID PartNumber BOM JAL AUG SEP OCT
FO01 Part01 BOM01 0 3000 6000 3500
------解决方案--------------------
你自己添加所有月份即可,另外你自己列的结果和你的数据有点不同
--master
select a.ID,a.PartNumber,a.BOM,b.Caption,sum(QtyCurrent) as QtyCurrent
into temp_fo_master
from Forecast a
inner join ForecastOrderArea b on a.ID = b.ID and a.BeginDate = b.BeginDate
group by a.ID,a.PartNumber,a.BOM,b.Caption
select ID,PartNumber,BOM,[JAL],[AUG],[SEP],[OCT]
from temp_fo_master
pivot
(
sum(QtyCurrent) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt
--detail
select a.BOM,b.ComponentId,a.Caption,a.QtyCurrent * b.Qty as Qty
into temp_fo_detail
from temp_fo_master a
inner join PartBOMDetail b on a.BOM = b.ID
--detail_dua
select BOM,ComponentId,[JAL],[AUG],[SEP],[OCT]
from temp_fo_detail
pivot
(
sum(Qty) for Caption in ([JAL],[AUG],[SEP],[OCT])
) as pvt
select b.ComponentId,a.Caption,a.QtyCurrent * b.Qty as Qty
into temp_fo_detail_dua
from temp_fo_master a
inner join PartBOMDetail b on a.BOM = b.ID