两表之间的数据转换
SQL 行转列
/*规则是:
将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
[解决办法]
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
;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
/*
StartDateEndDate
2008-09-20 22:15:00.0002008-09-20 22:16:00.000
2008-09-20 22:17:00.0002008-09-20 23:03:00.000
2008-09-20 23:04:00.0002008-09-20 23:05:00.000
2008-09-20 23:13:00.000NULL
*/
