如何在SQL2005中循环添加列

DECLARE @i int
set @i=0
WHILE @i<10
BEGIN
alter table test add lie+@i int
set @i=@i+1
END
就可以添加lie1,lie2,lie3,lie4……
@i是控制循环的次数，也可以理解为要添加的列数

************
补充：
************
1、建一个表，保存你要追加的字段名和字段类型
如：create table t_tmp(id int IDENTITY(1,1) not null,col_name varchar(100),col_type varchar(100));

2、把你想要动态追加的字段和类型全写入表中
如：
insert into t_tmp(col_name,col_type) values('t1','int');
insert into t_tmp(col_name,col_type) values('t2','date');
insert into t_tmp(col_name,col_type) values('t3','varchar(100)');
insert into t_tmp(col_name,col_type) values('t4','char(100)');

3、生成动态建表语句，并创建表
declare @str varchar(2000)
set @str='create table ttt('
select @str=@str + col_name+' '+col_type+',' from t_tmp
set @str=substring(@str,1,len(@str)-1)+')'
select @str
exec(@str)

------------------------------------------------------------------------------------------------------------

declare @min varchar(4),@max varchar(4), @sqlstr varchar(8000)
set @sqlstr = 'select FItemID'
select @min = min(years), @max = max(years) from #Happen3
while @min <= @max
begin
set @sqlstr = @sqlstr+',
sum(case when years = '+@min +' then(case when Months=1 then (FAmount / FQty)end)end) as Y'+@min +'P1Price,
sum(case when years = '+@min +' then(case when Months=2 then (FAmount / FQty)end)end) as Y'+@min +'P2Price,
sum(case when years = '+@min +' then(case when Months=3 then (FAmount / FQty)end)end) as Y'+@min +'P3Price,
sum(case when years = '+@min +' then(case when Months=4 then (FAmount / FQty)end)end) as Y'+@min +'P4Price,
sum(case when years = '+@min +' then(case when Months=5 then (FAmount / FQty)end)end) as Y'+@min +'P5Price,
sum(case when years = '+@min +' then(case when Months=6 then (FAmount / FQty)end)end) as Y'+@min +'P6Price,
sum(case when years = '+@min +' then(case when Months=7 then (FAmount / FQty)end)end) as Y'+@min +'P7Price,
sum(case when years = '+@min +' then(case when Months=8 then (FAmount / FQty)end)end) as Y'+@min +'P8Price,
sum(case when years = '+@min +' then(case when Months=9 then (FAmount / FQty)end)end) as Y'+@min +'P9Price,
sum(case when years = '+@min +' then(case when Months=10 then (FAmount / FQty)end)end) as Y'+@min +'P10Price,
sum(case when years = '+@min +' then(case when Months=11 then (FAmount / FQty)end)end) as Y'+@min +'P11Price,
sum(case when years = '+@min +' then(case when Months=12 then (FAmount / FQty)end)end) as Y'+@min +'P12Price'
set @min = @min+1
end
set @sqlstr = @sqlstr +' into ##Happen4 from #Happen3 where Not(FQty=0) group by FItemID'
exec(@sqlstr)