两表之间的数据转换

[aohan]

   /*规则是:
         将T1表中的记录转到T2中，不使用游标是否有办法处理？
         T1表中的第1记录填在T2中的startdate列上，第2条记录填写在enddate列上，
                 第3记录填在T2中的startdate列上，第4条记录填写在enddate列上
                ......
         依次类推，如果是记录条数是奇数时，T2的Enddate列填写Null
   */
   
   --1、测试表
   create table T1(sdate datetime )
   
   insert into T1 ( sdate )  VALUES  ( '2008-09-20 22:15:00'  )   
   insert into T1 ( sdate )  VALUES  ( '2008-09-20 22:16:00'  ) 
   insert into T1 ( sdate )  VALUES  ( '2008-09-20 22:17:00'  ) 
   insert into T1 ( sdate )  VALUES  ( '2008-09-20 23:03:00'  ) 
   insert into T1 ( sdate )  VALUES  ( '2008-09-20 23:04:00'  ) 
   insert into T1 ( sdate )  VALUES  ( '2008-09-20 23:05:00'  ) 
   insert into T1 ( sdate )  VALUES  ( '2008-09-20 23:13:00'  )    
   
   create table T2 (startdate datetime ,enddate datetime )
    
    
   --2、数据转换 
   /*这里进行@T1到@T2的转换
   
      ........
      
   */
   
   --3、查询数据表T2
   select * from T2
   
   --希望转换后查询 select * from T2  的结果如下
   /*
   startdate                       enddate
   2008-09-20 22:15:00             2008-09-20 22:16:00
   2008-09-20 22:17:00             2008-09-20 23:03:00
   2008-09-20 23:04:00             2008-09-20 23:05:00
   2008-09-20 23:13:00             null
   
   */
   
    --删除测试
   
   drop table T1
   drop table T2

[daiyueqiang2045]

   with cte as
   (
	   select *,ROW_NUMBER() over(order by T1.sdate) rn
	   from
	   T1 
   )
   insert into T2
   select t1.sdate startdate,t2.sdate enddate
   from
   cte t1 inner join cte t2 on t1.rn=t2.rn+1
   where t1.rn%2=1
   
    select * from T2

[中国风]

用left join

;with a
as
(
   select *,ROW_NUMBER()over(order by sdate) as Row
   from t1 

)
select a.sdate as StartDate,b.sdate as EndDate
from a 
	left join a as b on a.Row=b.Row-1 
where a.Row%2=1
/*
StartDate	EndDate
2008-09-20 22:15:00.000	2008-09-20 22:16:00.000
2008-09-20 22:17:00.000	2008-09-20 23:03:00.000
2008-09-20 23:04:00.000	2008-09-20 23:05:00.000
2008-09-20 23:13:00.000	NULL
*/

[chenjun1634]

学习了，但是“如果是记录条数是奇数时，T2的Enddate列填写Null”好像不符合。