帮我写select 语句－－－－－在线等待答案

[tenny]

存在两个表：
table1:(有两个字段，共有m行数据,id不可以重复的)
name = a
______________
id value
______________
7 2
8 3
9 4
. 5
. 6
. 5
m 9
______________

table2:（n列，1行数据）
name = b
_____________________________________
id pos1 pos2 pos3 .... posn
_____________________________________
1 7 8 9 m

使用select 语句和联结得出下列结果（表头为b表的表头，值为b表对应
pos列的值在a表id列中出现的id对应的value）:
_____________________________________
id pos1 pos2 pos3 .... posn
_____________________________________
1 2 3 4 .... 9

[futulove]

select b.id,pos1=(select value from a where a.id=b.pos1),
pos2=(select value from a where a.id=b.pos2),
pos3=(select value from a where a.id=b.pos3),
.
.
posm=(select value from a where a.id=b.posm)
from b

[zheninchangjiang]

declare @sql varchar(8000),@i int
set @sql=''
while @i<=n
begin
set @sql=@sql+',bb.pos'+cast(@i as varchar)+'=(select value from a where id=bb.pos'+cast(@i as varchar)+')'
set @i=@i+1
enf
set @sql=stuff(@sql,1,1,'')
exec('update bb set '+@sql+' from b bb')