try:
declare @a varhcar(200)
set @a = 'A01;B01;C03;A02;...'
declare @s varchar(10)
set @s = 'select space(1) where 1=2 '
declare @i int
set @i = charindex(';',@a)
while @i > 0
begin
set @s = @s + 'union Select ' + left(@a,@i)
set @a = substring(@a,@i+1,len(@a) - @i)
set @i = charindex(';',@a)
end
exec(@s)
declare @a varhcar(200)
set @a = 'A01;B01;C03;A02;...'
declare @s varchar(10)
set @s = 'select space(1) where 1=2 '
declare @i int
set @i = charindex(';',@a)
while @i > 0
begin
set @s = @s + 'union Select ' + left(@a,@i)
set @a = substring(@a,@i+1,len(@a) - @i)
set @i = charindex(';',@a)
end
exec(@s)
--创建数据测试环境
create table 表1(codes varchar(8000))
insert into 表1
select 'A01;B01;C03;A02'--为表分列准备临时表
select top 8000 id=identity(int,1,1) into #tb
from
(select top 100 id from syscolumns) a
,(select top 100 id from syscolumns) b
,(select top 100 id from syscolumns) c
--得到的结果
select substring(codes,b.id,charindex(';',codes+';',b.id)-b.id)
from 表1 a,#tb b
where substring(';'+codes,b.id,1)=';'drop table 表1,#tb
declare @nn int
declare @s varchar(8000)
declare @ss varchar(255)
set @n=5
set @nn=1
set @s='substring(codes,1,3)'
set @ss=''
select @ss=codes from cas
set @nn=len(@ss)
while @n<>@nn
begin
set @s=@s+' union '+substring(@ss,@n,3)
set @n=@n+4
select @s
end
exec('select '+@s+' from cas')
declare @n int
declare @nn int
declare @ns varchar(2)
declare @s varchar(8000)
declare @ss varchar(8000)
set @n=5set @nn=1
set @ss=''
select @ss=codes from cas
set @s='substring(codes,1,3) from cas '
set @nn=len(@ss)-2
while @n<>@nn
begin
set @ns=cast(@n as varchar)
set @s=@s+' union select substring(codes,'+@ns+',3) from cas '
set @n=@n+4
end
--select @s
exec('select '+@s)
declare @p1 varchar(200)declare @t1 smallintdeclare @p2 varchar(10)set @p1='A01;B01;C03;A02;'set @t1=charindex(';',@p1)while @t1>0
begin
set @p2=left(@p1,@t1-1) exec('insert into #p1 values('''+@p2+''')')
if len(@p1)>@t1
begin
set @p1=right(@p1,len(@p1)-@t1)
set @t1=charindex(';',@p1)
end
else
set @t1=0
endselect * from #p1