declare @t table(col varchar(50))
insert into @t select 'aa,bb,cc'
union all select 'aaa,bb'
union all select 'aaa'
declare @i int,@s varchar(1000)
set @i=0
select col into #t from @t
while @@rowcount> 0 --用记录会多一列出来
begin
select @i=@i+1,@s= 'alter table #t add [col'+cast(@i as varchar)+ '] varchar(100) '
exec (@s)
--print @s
set @s= 'update #t set col'+cast(@i as varchar)+ '=left(col,charindex( '','',col+ '','')-1),col=stuff(col,1,charindex( '','',col+ '',''),'''') where col>'''''
exec (@s)
--print @s
end
--set @s= 'alter table #t drop column col,col '+cast(@i as varchar)
--exec(@s)
insert into @t select 'aa,bb,cc'
union all select 'aaa,bb'
union all select 'aaa'
declare @i int,@s varchar(1000)
set @i=0
select col into #t from @t
while @@rowcount> 0 --用记录会多一列出来
begin
select @i=@i+1,@s= 'alter table #t add [col'+cast(@i as varchar)+ '] varchar(100) '
exec (@s)
--print @s
set @s= 'update #t set col'+cast(@i as varchar)+ '=left(col,charindex( '','',col+ '','')-1),col=stuff(col,1,charindex( '','',col+ '',''),'''') where col>'''''
exec (@s)
--print @s
end
--set @s= 'alter table #t drop column col,col '+cast(@i as varchar)
--exec(@s)
insert into @t select 'aa,bb,cc'
union all select 'aaa,bb'
union all select 'aaa'
declare @i int,@s varchar(1000)
set @i=0
select col into #t from @t
while exists(select 1 from #t where col>'')
begin
select @i=@i+1,@s= 'alter table #t add [col'+cast(@i as varchar)+ '] varchar(100) '
exec (@s)
--print @s
set @s= 'update #t set col'+cast(@i as varchar)+ '=left(col,charindex( '','',col+ '','')-1),col=stuff(col,1,charindex( '','',col+ '',''),'''') where col>'''''
exec (@s)
--print @s
end
select * from #t--drop table #t(所影响的行数为 3 行)
(所影响的行数为 3 行)
(所影响的行数为 3 行)
(所影响的行数为 2 行)
(所影响的行数为 1 行)col col1 col2 col3
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
aa bb cc
aaa bb NULL
aaa NULL NULL(所影响的行数为 3 行)
set @s= 'alter table #t drop column col,col '+cast(@i as varchar)
exec(@s)
--如果是删除第一列,这样改一下
set @s= 'alter table #t drop column col'
exec(@s)
insert into @t
select 'aa,bb,cc '
union all select 'aaa,bb '
union all select 'aaa '
declare @i int,@s varchar(700)
set @i=0
select col into #t from @t
while @@rowcount> 0
begin
select @i=@i+1 ,
@s= 'alter table #t add col'+cast(@i as varchar)+ ' varchar(100) '
print(@s)
exec (@s)
set @s= 'update #t set col'+cast(@i as varchar)+'=left(col,charindex('' '',col+ '' '')-1),col=stuff(col,1,charindex( '' '',col+ '' ''), '' '') where col> '' '''
print(@s)
exec (@s)
end
set @s= 'alter table #t drop column col'+cast(@i as varchar)
print(@s)
exec(@s)
select * from #t
drop table #t