方法1:
select 学号1,姓名1 into table_2 from
(SELECT 学号1,姓名1 FROM 表
UNION
SELECT 学号2,姓名2 FROM 表 )as temp
另外做了个表table_2来存结果
方法二:游标 (他们合成一张表再导回原表,原表性质由四列变俩列)
select 学号1,姓名1 into table_2 from
(select 学号1,姓名1 from 表
union
select 学号2,姓名2 from 表 ) as temp
go
delete from 表
go
alter table 表
DROP COLUMN 学号2,姓名2
go
declare t_cur cursor
for
select 学号1,姓名1 from table_2
open t_cur
declare @aa varchar(10),@bb varchar(10)
fetch next from t_cur into @aa,@bb
while @@fetch_status=0
begin
insert into 表 (学号1,姓名1) values(@aa,@bb)
fetch next from t_cur into @aa,@bb
end
close t_cur
deallocate t_cur
go
drop table table_2
方法三:上面的有点繁琐,后来再想了会,还是这个最简单了
select 学号1,姓名1 into table_2 from
(select 学号1,姓名1 from 表
union
select 学号2,姓名2 from 表 ) as temp
go
drop table 表
go
select * into 表 from table_2
go
drop table table_2