请教高手，这句交叉表的SQL应该怎么写？马上给分

[BCDV]

table1:
ID WareID WareName
1 001 材料1
2 002 材料2
table2:
ID IndexID Pdate P1 P2 P3 P4 Pn1 Pn2 Pn3 Pn4
1 1 2005-01-01 A B C D 1 2 3 4
2 1 2005-02-02 B A D C 3 4 2 1
3 2 2005-03-04 A C D B 4 4 2 1
要得到下面的结果：
ID WareID WareName 2005-01-01 2005-02-02 2005-01-01 2005-02-02 2005-03-04 2005-03-04
1 001 材料1 ABCD BADC 1234 3421
2 002 材料2 ACDB 4421

应该怎么写SQL？

[spring_504]

select a.ID,a.WareID,a.WareName,
N'2005-01-01'=(select P1+P2+P3+P4 from table2 where Pdate='2005-01-01' and IndexID=b.IndexID ),
N'2005-02-02'=(select P1+P2+P3+P4 from table2 where Pdate='2005-02-02' and IndexID=b.IndexID ),
N'2005-01-01'=(select Pn1+Pn2+Pn3+Pn4 from table2 where Pdate='2005-01-01' and IndexID=b.IndexID ),
N'2005-02-02'=(select Pn1+Pn2+Pn3+Pn4 from table2 where Pdate='2005-02-02' and IndexID=b.IndexID ),
N'2005-03-04'=(select P1+P2+P3+P4 from table2 where Pdate='2005-03-04' and IndexID=b.IndexID ),
N'2005-03-04'=(select Pn1+Pn2+Pn3+Pn4 from table2 where Pdate='2005-03-04' and IndexID=b.IndexID )
from table1 a inner join table2 b on a.ID=b.IndexID

[子陌红尘]

--生成测试数据
create table table1(ID int,WareID varchar(10),WareName varchar(10))
insert into table1 select 1,'001','材料1'
insert into table1 select 2,'002','材料2'

create table table2(ID int,IndexID int,Pdate datetime,P1 char(1),P2 char(1),P3 char(1),P4 char(1),Pn1 int,Pn2 int,Pn3 int,Pn4 int)
insert into table2 select 1,1,'2005-01-01','A','B','C','D',1,2,3,4
insert into table2 select 2,1,'2005-02-02','B','A','D','C',3,4,2,1
insert into table2 select 3,2,'2005-03-04','A','C','D','B',4,4,2,1


--执行交叉表查询
declare @s varchar(8000)
set @s = ''

select
@s = @s + ',['+a.Pdate+']=max(case b.Pdate when '''+a.Pdate+''' then b.P1+b.P2+b.P3+b.P4 end)'
+ ',['+a.Pdate+']=max(case b.Pdate when '''+a.Pdate+''' then cast(b.Pn1 as varchar(2))+cast(b.Pn2 as varchar(2))+cast(b.Pn3 as varchar(2))+cast(b.Pn4 as varchar(2)) end)'
from
(select distinct Pdate=convert(char(10),Pdate,120) from table2) a

set @s = 'select a.ID,a.WareID,a.WareName'+@s+' from table1 a,table2 b where a.ID=b.IndexID group by a.ID,a.WareID,a.WareName'

exec(@s)



--输出结果
ID WareID WareName 2005-01-01 2005-01-01 2005-02-02 2005-02-02 2005-03-04 2005-03-04
1 001 材料1 ABCD 1234 BADC 3421
2 002 材料2 ACDB 4421

[vivianfdlpw]

create table table1
(
ID int,
WareID varchar(10),
WareName varchar(10)
)
create table table2
(
ID int,
IndexID int,
pdate varchar(10),
p1 varchar(10),
p2 varchar(10),
p3 varchar(10),
p4 varchar(10),
Pn1 varchar(10),
Pn2 varchar(10),
Pn3 varchar(10),
Pn4 varchar(10)
)
insert table1
select 1,'001','材料1' union
select 2,'002','材料2'
insert table2
select 1,1,'2005-01-01','A','B','C','D','1','2','3','4' union
select 2,1,'2005-02-02','B','A','D','C','3','4','2','1' union
select 3,2,'2005-03-04','A','C','D','B','4','4','2','1'

declare @sql varchar(2000)
set @sql=''
select @sql=@sql+',['+pdate+']=max(case when B.[pdate]='''+pdate+''' then B.P1+B.P2+B.P3+B.P4 else '''' end'+')'+
+',['+pdate+']=max(case when B.[pdate]='''+pdate+''' then B.Pn1+B.Pn2+B.Pn3+B.Pn4 else '''' end'+')'
from table2
group by IndexID,pdate
set @sql=' select A.*'+@sql+' from table1 A join table2 B on A.id=B.IndexID group by A.ID,A.WareID,A.WareName'
--print @sql
exec(@sql)

--删除测试环境
drop table table1,table2

--结果
/*
ID WareID WareName 2005-01-01 2005-01-01 2005-02-02 2005-02-02 2005-03-04 2005-03-04
----------- ---------- ---------- ---------------------------------------- ------------------------------
1 001 材料1 ABCD 1234 BADC 3421
2 002 材料2 ACDB 4421

*/

[lys1706228]

2005-01-01 2005-02-02 2005-01-01 2005-02-02 这个不好办啊

[点点星灯]

declare @table1 table(ID int,WareID varchar(20),WareName varchar(20))
insert @table1 values(1,'001','材料1')
insert @table1 values(2,'002','材料2')
--select * from @table1



declare @table2 table(ID int,IndexID int,Pdate datetime,P1 varchar(2),P2 varchar(2),P3 varchar(2),P4 varchar(2),Pn1 varchar(2),Pn2 varchar(2),Pn3 varchar(2),Pn4 varchar(2))
insert @table2 values(1,1,'2005-01-01','A','B','C','D','1','2','3','4')
insert @table2 values(2,1,'2005-02-02','B','A','D','C','3','4','2','1')
insert @table2 values(3,2,'2005-03-04','A','C','D','B','4','4','2','1')
--select * from @table2

select *, p1+p2+p3+p4 as 'P1234',Pn1+Pn2+Pn3+Pn4 as 'Pn1234' into #t from @table2
select * from #t


select a.*,
'2005-01-01'=min(case b.IndexID when 1 then [P1234] else '' end),
'2005-01-01'=min(case b.IndexID when 1 then [Pn1234] else '' end),
'2005-02-02'=max(case b.IndexID when 1 then [P1234] else '' end),
'2005-02-02'=max(case b.IndexID when 1 then [Pn1234] else '' end),
'2005-03-04'=max(case b.IndexID when 2 then [P1234] else '' end),
'2005-03-04'=max(case b.IndexID when 2 then [Pn1234] else '' end)
from @table1 a,#t b
where a.ID=b.IndexID
group by a.ID,a.WareID,a.WareName


drop table #t

[点点星灯]

--结果
ID WareID WareName 2005-01-01 2005-01-01 2005-02-02 2005-02-02 2005-03-04 2005-03-04
----------- -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- ----------
1 001 材料1 ABCD 1234 BADC 3421
2 002 材料2 ACDB 4421

[rivery]

以上的在列的顺序上没有按照搂住的要求。
create table #table1(id int,wareid char(3),warename varchar(20))
create table #table2(id int,indexid int,pdate datetime,p1 varchar(20),p2 varchar(20),p3 varchar(20),p4 varchar(20),
pn1 varchar(20),pn2 varchar(20),pn3 varchar(20),pn4 varchar(20))

insert into #table1
select 1,'001','材料1'
union all select 2,'002','材料2'
insert into #table2
select 1, 1,'2005-01-01','A','B','C','D','1','2','3','4'
union all select 2,1,'2005-02-02','B','A','D','C','3','4','2','1'
union all select 3,2,'2005-03-04','A','C','D','B','4','4','2','1'

declare @sql varchar(8000),@sql2 varchar(8000)
select @sql=''
select @sql=@sql+',max(case pdate when '''+convert(varchar(10),pdate,120)+''' then p1+p2+p3+p4 else NULL end) as '''+convert(varchar(10),pdate,120)+''''
from #table2
group by indexid,pdate
select @sql2=''
select @sql2=@sql2+',max(case pdate when '''+convert(varchar(10),pdate,120)+''' then pn1+pn2+pn3+pn4 else NULL end) as '''+convert(varchar(10),pdate,120)+''''
from #table2
group by indexid,pdate

--print 'select a.*'+@sql+@sql2+' from #table1 a, #table2 b where a.id=b.indexid group by a.id,a.wareid,a.warename'
exec('select a.*'+@sql+@sql2+' from #table1 a,#table2 b where a.id=b.indexid group by a.id,a.wareid,a.warename')
/*
结果
id wareid warename 2005-01-01 2005-02-02 2005-03-04 2005-01-01 2005-02-02 2005-03-04
--------------------------------------------------------------------------------------------------------------------
1 001 材料1 ABCD BADC NULL 1234 3421 NULL
2 002 材料2 NULL NULL ACDB NULL NULL 4421

*/

[rivery]

我的顺序也不对，看错了。其他人给调整吧，没时间了。