求一个存储过程

--假设你的一个表为 table_a (列 cno 是 int格式)
--最好你每个学生编个号,那才确定唯一性
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_format_trans]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_format_trans]
GO
create procedure sp_format_trans
as
set nocount on
create table #temp(name nvarchar(10),col_1 int,col_2 int,col_3 int,col_4 int,col_5 int)
declare @cur int,@name nvarchar(10),@cno int
declare mycursor cursor for select * from table_a
open mycursor
fetch next from mycursor into @cur,@name,@cno
while @@fetch_status = 0
begin
if exists(select * from #temp where name=@name)
begin
if @cur=1
begin
update #temp set col_1=@cno where name=@name
end
if @cur=2
begin
update #temp set col_2=@cno where name=@name
end
if @cur=3
begin
update #temp set col_3=@cno where name=@name
end
if @cur=4
begin
update #temp set col_4=@cno where name=@name
end
if @cur=5
begin
update #temp set col_5=@cno where name=@name
end
end
else
begin
insert into #temp(name,col_1) values(@name,@cno)
end
fetch next from mycursor into @cur,@name,@cno
end
close mycursor
deallocate mycursor
select * from #temp
drop table #temp