请问怎么样生成这样的交叉表？

[tanst]

数据库中的表：
id value week
2 P2 1
1 P1 2
1 P1 3
1 P1 4
1 P1 5
1 P1 6
1 P1 7

要生成的表：

week: 1 2 3 4 5 6 7 1 2 3 4 5 6 7
2 1 1 1 1 1 1 P2 P1 P1 P1 P1 P1 P1

怎么在存贮过程中实现呢？

[tanst]

再补充一下，目标表应该是这样：
表头: i1 i2 i3 i4 i5 i6 i7 v1 v2 v3 v4 v5 v6 v7
一条记录 2 1 1 1 1 1 1 P2 P1 P1 P1 P1 P1 P1

[pengdali]

create table # (id int,value char(2),week int)
insert # values(2,'P2',1)
insert # values(1,'P1', 2)
insert # values(1,'P1', 3)
insert # values(1,'P1', 4)
insert # values(1,'P1', 5)
insert # values(1,'P1', 6)
insert # values(1,'P1', 7)

declare @sql1 varchar(8000),@sql2 varchar(8000)
select @sql1='',@sql2=''
select @sql1=@sql1 + ',sum(case week when '+cast(week as varchar)+' then id else 0 end) i'+cast(week as varchar),
@sql2=@sql2 + ',max(case week when '+cast(week as varchar)+' then value end) v'+cast(week as varchar)
from (select distinct week from #) as a

set @sql1=right(@sql1,len(@sql1)-1)

exec('select '+@sql1+@sql2+' from #')
go



drop table #

[newson]

up