现有两个表#t1和#t2
#t1:
sdate dept10 dept11 ....dept25
20051205 0 0 0
20051206 0 0 0
20051208 0 0 0
#t2:
sdate dept value
20051205 15 -157.81
20051205 20 203.05
20051206 21 3289.02
20051208 22 -149.00
20051208 23 -445.12
20051208 24 369.48
请问如何将满足以下条件的表#t2value的数据update进#t1dept列内
条件1:#t1.sdate=#t2.sdate
条件2:right(#t1列名,2)=#t2.dept
#t1:
sdate dept10 dept11 ....dept25
20051205 0 0 0
20051206 0 0 0
20051208 0 0 0
#t2:
sdate dept value
20051205 15 -157.81
20051205 20 203.05
20051206 21 3289.02
20051208 22 -149.00
20051208 23 -445.12
20051208 24 369.48
请问如何将满足以下条件的表#t2value的数据update进#t1dept列内
条件1:#t1.sdate=#t2.sdate
条件2:right(#t1列名,2)=#t2.dept
set value = dept
from #t1
where #t1.sdate= #t2.sdate
and right(#t1.列名,2)=#t2.dept
insert into #t1 select '20051205',0,0,0
insert into #t1 select '20051206',0,0,0
insert into #t1 select '20051208',0,0,0
create table #t2(sdate varchar(10),dept int,value numeric(6,2))
insert into #t2 select '20051205',10,-157.81
insert into #t2 select '20051205',11,203.05
insert into #t2 select '20051206',25,3289.02
insert into #t2 select '20051208',10,-149.00
insert into #t2 select '20051208',11,-445.12
insert into #t2 select '20051208',25,369.48declare @s varchar(8000)
set @s=''select @s=@s+'update a set dept'+rtrim(dept)+'=b.value from #t1 a,#t2 b where a.sdate=b.sdate and b.dept='+rtrim(dept)+'
' from #t2 group by deptexec(@s)select * from #t1/*
sdate dept10 dept11 dept25
---------- -------- -------- --------
20051205 -157.81 203.05 .00
20051206 .00 .00 3289.02
20051208 -149.00 -445.12 369.48
*/drop table #t1,#t2
set dept10=(case when #t2.dept=10 then #t2.value else 0 end),
dept11=(case when #t2.dept=11 then #t2.value else 0 end),
dept12=(case when #t2.dept=12 then #t2.value else 0 end),
.
.
.
.
.
from #t2
where #t1.sdate= #t2.sdate