create table #yourtable(ID int,Content varchar(4000))
insert into #yourtable(ID,Content)
select 1,'22|5000|3000'
union all select 2,'1|35|200|2'
union all select 3,'802|22'
union all select 4,'213|354|2002|22|500'
--实际操作SQL
declare @sql nvarchar(4000),@i int
set @i=1
while exists(select 1 from #yourtable where Content<>'')
begin
set @sql='alter table #yourtable add Data'+convert(varchar,@i)+' int'
exec(@sql)
set @sql='declare @loc int update #yourtable set @loc=charindex(''|'',Content),Data'
+convert(varchar,@i)+'=convert(int,case @loc when 0 then Content else '
+'substring(Content,1,@loc-1) end),Content=case @loc when 0 then '''' else '
+'substring(Content,@loc+1,len(Content)-@loc) end where Content<>'''''
exec(@sql)
set @i=@i+1
end
select * from #yourtable
--删除演示数据
drop table #yourtable
--结果
ID Data1 Data2 Data3 Data4 Data5
----------- ----------- ----------- ----------- ----------- -----------
1 22 5000 3000 NULL NULL
2 1 35 200 2 NULL
3 802 22 NULL NULL NULL
4 213 354 2002 22 500