declare @studentnum varCHAR(9),@course varchar(10),@achievement tinyint,@classorder tinyint
declare @pstudentnum varchar(9),@allcourse varchar(60)
declare allachievement cursor for
select studentnum,course,achievement,classorder
from tempachievement
where indate=convert(char(10),getdate(),120) and achievement>0
order by studentnum,course
OPEN allachievement
FETCH NEXT FROM allachievement
INTO @studentnum, @course, @achievement, @classorder
set @pstudentnum=@studentnum
set @allcourse=''
WHILE @@FETCH_STATUS = 0
BEGIN
if (@pstudentnum <> @studentnum)
begin
if (len(@allcourse)>0)
begin
insert achievementaddfields(studentnum,addfields)
values(@pstudentnum,@allcourse)
--set addfields=@allcourse
--where studentnum=@pstudentnum
end
set @pstudentnum=@studentnum
--set @allcourse=@course+@achievement+@classorder
--set @allcourse=''
set @allcourse=rtrim(@course) + ',' + ltrim(str(@achievement)) + ',' + ltrim(str(@classorder)) + ';'
FETCH NEXT FROM allachievement
INTO @studentnum, @course, @achievement, @classorder
end
else
begin
set @allcourse=@allcourse + rtrim(@course) + ',' + ltrim(str(@achievement)) + ',' + ltrim(str(@classorder)) + ';'
FETCH NEXT FROM allachievement
INTO @studentnum, @course, @achievement, @classorder
end
end
if (len(@allcourse)>0)
begin
insert achievementaddfields(studentnum,addfields)
values(@pstudentnum,@allcourse)
--set addfields=@allcourse
--where studentnum=@pstudentnum
end
CLOSE allachievement
DEALLOCATE allachievement