--不知道楼主能不能接受游标的速度
/*测试数据*/
--表1
create table 表1 (EventPerSystemID varchar(10),[DateTime] datetime,[MessageID] varchar(10),Message varchar(30),EventMessage varchar(20))
insert into 表1 select '29551188','2006-04-24 10:36:33 AM','8303593','China','SelectCountry'
insert into 表1 select '29551189','2006-04-24 10:36:35 AM','8303594','zhejiang','SelectProvince'
insert into 表1 select '29551191','2006-04-24 10:36:35 AM','8308134','space','hangzhou'
insert into 表1 select '29551193','2006-04-24 10:36:38 AM','8303592','tian','wenzhou'
insert into 表1 select '29551194','2006-04-24 10:36:38 AM','8303593','USA','SelectCountry'
insert into 表1 select '29551195','2006-04-24 10:36:39 AM','8303594','Chicao','SelectProvince'
insert into 表1 select '29551196','2006-04-24 10:36:52 AM','8303135','E2','Washition'
insert into 表1 select '29551197','2006-04-24 10:36:53 AM','3273491','[FUNCTION_RESULT]','Maya'
insert into 表1 select '29551198','2006-04-24 10:36:54 AM','3273492','Completed FSA','Newyork'--表2
create table 表2 (EventPerSystemID varchar(10),[DateTime] datetime,[Procedure] varchar(10),Application varchar(10),EventMessage varchar(20))/*游标insert*/
declare @id varchar(10),@date datetime,@messageID varchar(10),@message varchar(30),@event varchar(20),@application varchar(20),@procedure varchar(20)
set @application =''
set @procedure=''
declare c1 cursor scroll for
select * from 表1 order by EventPerSystemID
open c1
fetch first from c1 into @id,@date,@messageID,@message,@event
while @@fetch_status=0
begin
if @event='SelectCountry'
begin
set @application=@message
fetch next from c1 into @id,@date,@messageID,@message,@event
end
else if @event='SelectProvince'
begin
set @procedure=@message
fetch next from c1 into @id,@date,@messageID,@message,@event
end
else
begin
insert into 表2 select @id,@date,@procedure,@application,@event
fetch next from c1 into @id,@date,@messageID,@message,@event
end
end
close c1
deallocate c1/*Check the result:*/
select * from 表2EventPerSystemID DateTime Procedure Application EventMessage
--------------------------------------------------------------------------------
29551191 2006-04-24 10:36:35.000 zhejiang China hangzhou
29551193 2006-04-24 10:36:38.000 zhejiang China wenzhou
29551196 2006-04-24 10:36:52.000 Chicao USA Washition
29551197 2006-04-24 10:36:53.000 Chicao USA Maya
29551198 2006-04-24 10:36:54.000 Chicao USA Newyork
/*测试数据*/
--表1
create table 表1 (EventPerSystemID varchar(10),[DateTime] datetime,[MessageID] varchar(10),Message varchar(30),EventMessage varchar(20))
insert into 表1 select '29551188','2006-04-24 10:36:33 AM','8303593','China','SelectCountry'
insert into 表1 select '29551189','2006-04-24 10:36:35 AM','8303594','zhejiang','SelectProvince'
insert into 表1 select '29551191','2006-04-24 10:36:35 AM','8308134','space','hangzhou'
insert into 表1 select '29551193','2006-04-24 10:36:38 AM','8303592','tian','wenzhou'
insert into 表1 select '29551194','2006-04-24 10:36:38 AM','8303593','USA','SelectCountry'
insert into 表1 select '29551195','2006-04-24 10:36:39 AM','8303594','Chicao','SelectProvince'
insert into 表1 select '29551196','2006-04-24 10:36:52 AM','8303135','E2','Washition'
insert into 表1 select '29551197','2006-04-24 10:36:53 AM','3273491','[FUNCTION_RESULT]','Maya'
insert into 表1 select '29551198','2006-04-24 10:36:54 AM','3273492','Completed FSA','Newyork'--表2
create table 表2 (EventPerSystemID varchar(10),[DateTime] datetime,[Procedure] varchar(10),Application varchar(10),EventMessage varchar(20))/*游标insert*/
declare @id varchar(10),@date datetime,@messageID varchar(10),@message varchar(30),@event varchar(20),@application varchar(20),@procedure varchar(20)
set @application =''
set @procedure=''
declare c1 cursor scroll for
select * from 表1 order by EventPerSystemID
open c1
fetch first from c1 into @id,@date,@messageID,@message,@event
while @@fetch_status=0
begin
if @event='SelectCountry'
begin
set @application=@message
fetch next from c1 into @id,@date,@messageID,@message,@event
end
else if @event='SelectProvince'
begin
set @procedure=@message
fetch next from c1 into @id,@date,@messageID,@message,@event
end
else
begin
insert into 表2 select @id,@date,@procedure,@application,@event
fetch next from c1 into @id,@date,@messageID,@message,@event
end
end
close c1
deallocate c1/*Check the result:*/
select * from 表2EventPerSystemID DateTime Procedure Application EventMessage
--------------------------------------------------------------------------------
29551191 2006-04-24 10:36:35.000 zhejiang China hangzhou
29551193 2006-04-24 10:36:38.000 zhejiang China wenzhou
29551196 2006-04-24 10:36:52.000 Chicao USA Washition
29551197 2006-04-24 10:36:53.000 Chicao USA Maya
29551198 2006-04-24 10:36:54.000 Chicao USA Newyork
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货