server多列转行 sql_SQL Server 2008列转行问题

我统计出公时表的数据,如下createtableoperator(begin_timedatetime,end_timedatetime,op_idchar(4),work_timeint)insertintooperatorselect'2013-03-0100:00:00','2013-03-0200:00:0...

我统计出公时表的数据,如下

create table operator(

begin_time datetime,

end_time datetime,

op_id char(4),

work_time int

)

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','1001',7

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','1002',8

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','1003',6

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','2001',8

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','2002',8

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','2003',7

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','2004',8

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3001',6

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3002',7

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3003',8

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3004',5

insert into operator select '2013-03-01 00:00:00','2013-03-02 00:00:00','3005',6

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1001',7

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1002',8

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1003',6

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1005',9

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','1007',6

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','2002',9

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','2003',7

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','2004',5

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','2006',3

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','3003',8

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','3005',7

insert into operator select '2013-03-02 00:00:00','2013-03-03 00:00:00','3007',10

然后我想把这个数据,转换成如下两种表的格式,请问如何处理?

第一张结果表中,手误多输入了一行开始结束时间,后面却是空的.(请大家自动屏蔽之),谢谢!

展开