create table AAN (num int ) insert into aan select Convert(int,rand()*100) insert into aan select Convert(int,rand()*100) insert into aan select Convert(int,rand()*100) insert into aan select Convert(int,rand()*100) insert into aan select Convert(int,rand()*100) insert into aan select Convert(int,rand()*100) insert into aan select Convert(int,rand()*100) go select num,identity(int,1,1) as ID into #a from aan go select a.num as Num1,b.num as Num2,c.num as Num3 from #a a left outer join #a b on a.id+1=b.id left outer join #a c on a.id+2=c.id where a.id%3=1
declare @t table(c int) insert into @t values(1) insert into @t values(2) insert into @t values(3) insert into @t values(4) insert into @t values(2) insert into @t values(3) insert into @t values(4) insert into @t values(2) insert into @t values(3) insert into @t values(4) select * from @t----变化 declare @Count int set @Count=0 declare @Value int declare @t2 table(tId int IDENTITY (1, 1) NOT NULL,c1 int,c2 int, c3 int) declare @Id int DECLARE TestCursor CURSOR FOR SELECT * from @t; open TestCursor; FETCH NEXT FROM TestCursor INTO @Value WHILE @@FETCH_STATUS = 0 BEGIN if @count%3=0 begin insert into @t2(c1) values(@Value) select @Id=@@identity end else begin if @count%3=1 update @t2 set c2=@Value if @Count%3=2 update @t2 set c3=@Value end set @Count=@Count+1
FETCH NEXT FROM TestCursor INTO @Value End CLOSE TestCursor DEALLOCATE TestCursor; select c1,c2,c3 from @t2
insert into aan select Convert(int,rand()*100)
insert into aan select Convert(int,rand()*100)
insert into aan select Convert(int,rand()*100)
insert into aan select Convert(int,rand()*100)
insert into aan select Convert(int,rand()*100)
insert into aan select Convert(int,rand()*100)
insert into aan select Convert(int,rand()*100) go
select num,identity(int,1,1) as ID into #a from aan
go
select a.num as Num1,b.num as Num2,c.num as Num3
from #a a left outer join #a b on a.id+1=b.id left outer join #a c on a.id+2=c.id
where a.id%3=1
insert into @t values(1)
insert into @t values(2)
insert into @t values(3)
insert into @t values(4)
insert into @t values(2)
insert into @t values(3)
insert into @t values(4)
insert into @t values(2)
insert into @t values(3)
insert into @t values(4)
select * from @t----变化
declare @Count int
set @Count=0
declare @Value int
declare @t2 table(tId int IDENTITY (1, 1) NOT NULL,c1 int,c2 int, c3 int)
declare @Id int
DECLARE TestCursor CURSOR FOR
SELECT * from @t;
open TestCursor;
FETCH NEXT FROM TestCursor
INTO @Value
WHILE @@FETCH_STATUS = 0
BEGIN
if @count%3=0
begin
insert into @t2(c1) values(@Value)
select @Id=@@identity
end
else
begin
if @count%3=1
update @t2 set c2=@Value
if @Count%3=2
update @t2 set c3=@Value
end
set @Count=@Count+1
FETCH NEXT FROM TestCursor
INTO @Value
End
CLOSE TestCursor
DEALLOCATE TestCursor;
select c1,c2,c3 from @t2