自动按字符串长度拆分-----高人们救救“火”啊~~~~~~~~~
create table bb (studentid varchar(100),markstr varchar(50))
insert into bb select
'2002081108 ', 'AAAAAAAAAAAAAAAAAAAAAAAAA ' union all select
'2002081107 ', 'BBBBBBBBBBBBBBBBBBBBBBBBB ' union all select
'2002081109 ', 'CCCCCCCCCCCCCCCCCCCCCCCCC ' union all select
'2002081110 ', 'DDDDDDDDDDDDDDDDDDCDCDDCC ' union all select
'2002081111 ', 'DDDEEDDDDDDDDDDEDDCDCDDCC '
如上建立数据表，想要把markstr拆分开来，得到如下的结果，并把结果集生成一张表，如下，如何写存储过程?
studentidno1no2no3no4no5no6no7no8no9no10no11no12no13no14no15no16no17no18no19no20no21no22no23no24no25
2002081108AAAAAAAAAAAAAAAAAAAAAAAAA
2002081107BBBBBBBBBBBBBBBBBBBBBBBBB
2002081109CCCCCCCCCCCCCCCCCCCCCCCCC
2002081110DDDDDDDDDDDDDDDDDDCDCDDCC
2002081111DDDEEDDDDDDDDDDEDDCDCDDCC
想要除
select studentid,substring(markstr,1,1) as no1,substring(markstr,2,1) as no2,substring(markstr,3,1) as no3,
substring(markstr,4,1) as no4,substring(markstr,5,1) as no5,substring(markstr,6,1) as no6,substring(markstr,7,1) as no7,
substring(markstr,8,1) as no8,substring(markstr,9,1) as no9,substring(markstr,10,1) as no10,substring(markstr,11,1) as no11,
substring(markstr,12,1) as no12,substring(markstr,13,1) as no13,substring(markstr,14,1) as no14,substring(markstr,15,1) as no15,
substring(markstr,16,1) as no16,substring(markstr,17,1) as no17,substring(markstr,18,1) as no18,substring(markstr,19,1) as no19,
substring(markstr,20,1) as no20,substring(markstr,21,1) as no21,substring(markstr,22,1) as no22,substring(markstr,23,1) as no23,
substring(markstr,24,1) as no24,substring(markstr,25,1) as no25 into aa from bb
类似的以外的答案~~~~~~~~~~~~~~~~~~~~~~这个方法太麻烦，而且，总项数是写死了的，如果增加26项，就要改，所以希望存储过程能按markstr的长度自动拆分，拜托各位了，帮帮忙*_*
[解决办法]
declare @i int
declare @t int
declare @sql varchar(8000)
set @sql= ' '
set @t=1
select @i=max(len(markstr)) from bb
while(@t <=@i)
begin
set @sql=@sql+ ',substring(markstr, '+cast(@t as varchar)+ ',1) as no '+cast(@t as varchar)
set @t=@t+1
end
set @sql= 'select studentid, '+stuff(@sql,1,1, ' ')+ ' into aa from bb '
exec(@sql)
