首页 新闻 搜索 专区 学院

求教 SQL Server 考勤数据处理

0
悬赏园豆:10 [待解决问题]

以下为数据一张考勤表

create table punch_table (card_no varchar(50),
punch_date datetime,
punch_time datetime )
select * from punch_table;

insert into punch_table values('10001','2014-12-12','2014-12-12 07:59:00')
insert into punch_table values('10001','2014-12-12','2014-12-12 08:32:00')
insert into punch_table values('10001','2014-12-12','2014-12-12 12:39:07')
insert into punch_table values('10001','2014-12-12','2014-12-12 13:54:00')
insert into punch_table values('10001','2014-12-12','2014-12-12 15:08:00')
insert into punch_table values('10001','2014-12-12','2014-12-12 19:52:00')
insert into punch_table values('10002','2014-12-12','2014-12-12 07:59:00')
insert into punch_table values('10002','2014-12-12','2014-12-12 09:35:00')
insert into punch_table values('10002','2014-12-12','2014-12-12 12:39:07')
insert into punch_table values('10002','2014-12-12','2014-12-12 18:34:00')
insert into punch_table values('10002','2014-12-12','2014-12-12 19:08:00')
insert into punch_table values('10002','2014-12-12','2014-12-12 19:52:00')
insert into punch_table values('10003','2014-12-12','2014-12-12 06:54:00')
insert into punch_table values('10003','2014-12-12','2014-12-12 08:22:00')
insert into punch_table values('10003','2014-12-12','2014-12-12 12:34:00')
insert into punch_table values('10003','2014-12-12','2014-12-12 19:58:00')
insert into punch_table values('10004','2014-12-12','2014-12-12 02:08:00')
insert into punch_table values('10004','2014-12-12','2014-12-12 09:52:00')
insert into punch_table values('10004','2014-12-12','2014-12-12 17:32:00')
insert into punch_table values('10004','2014-12-12','2014-12-12 19:31:00')
insert into punch_table values('10005','2014-12-12','2014-12-12 11:39:00')
insert into punch_table values('10005','2014-12-12','2014-12-12 12:34:00')
insert into punch_table values('10005','2014-12-12','2014-12-12 23:08:00')
insert into punch_table values('10005','2014-12-12','2014-12-12 23:52:00')
insert into punch_table values('10001','2014-12-13','2014-12-13 02:23:00')
insert into punch_table values('10001','2014-12-13','2014-12-13 03:31:00')
insert into punch_table values('10001','2014-12-13','2014-12-13 22:21:00')
insert into punch_table values('10001','2014-12-13','2014-12-13 13:04:00')
insert into punch_table values('10001','2014-12-14','2014-12-14 15:08:00')
insert into punch_table values('10001','2014-12-14','2014-12-14 19:24:00')
insert into punch_table values('10002','2014-12-13','2014-12-13 08:25:00')
insert into punch_table values('10002','2014-12-14','2014-12-14 07:32:00')
insert into punch_table values('10002','2014-12-14','2014-12-14 12:39:07')
insert into punch_table values('10002','2014-12-14','2014-12-14 17:34:00')
insert into punch_table values('10002','2014-12-14','2014-12-14 19:01:00')
insert into punch_table values('10003','2014-12-13','2014-12-13 10:02:00')
insert into punch_table values('10003','2014-12-13','2014-12-13 19:04:00')
insert into punch_table values('10003','2014-12-13','2014-12-13 23:35:00')
insert into punch_table values('10003','2014-12-14','2014-12-14 12:34:00')
insert into punch_table values('10003','2014-12-14','2014-12-14 13:23:00')
insert into punch_table values('10004','2014-12-13','2014-12-13 01:58:00')
insert into punch_table values('10004','2014-12-13','2014-12-13 03:51:00')
insert into punch_table values('10004','2014-12-13','2014-12-13 17:02:00')
insert into punch_table values('10004','2014-12-14','2014-12-14 19:38:00')
insert into punch_table values('10005','2014-12-13','2014-12-13 08:30:00')
insert into punch_table values('10005','2014-12-13','2014-12-13 12:34:00')
insert into punch_table values('10005','2014-12-13','2014-12-13 13:08:00')
insert into punch_table values('10005','2014-12-13','2014-12-13 18:52:00')

现在的输出格式为:

card_no punch_date punch_time
10001 2014-12-12 00:00:00.000 2014-12-12 07:59:00.000
10001 2014-12-12 00:00:00.000 2014-12-12 08:32:00.000
10001 2014-12-12 00:00:00.000 2014-12-12 12:39:07.000
10001 2014-12-12 00:00:00.000 2014-12-12 13:54:00.000
10001 2014-12-12 00:00:00.000 2014-12-12 15:08:00.000
10001 2014-12-12 00:00:00.000 2014-12-12 19:52:00.000
10002 2014-12-12 00:00:00.000 2014-12-12 07:59:00.000
10002 2014-12-12 00:00:00.000 2014-12-12 09:35:00.000
10002 2014-12-12 00:00:00.000 2014-12-12 12:39:07.000
10002 2014-12-12 00:00:00.000 2014-12-12 18:34:00.000
10002 2014-12-12 00:00:00.000 2014-12-12 19:08:00.000
10002 2014-12-12 00:00:00.000 2014-12-12 19:52:00.000
10003 2014-12-12 00:00:00.000 2014-12-12 06:54:00.000
10003 2014-12-12 00:00:00.000 2014-12-12 08:22:00.000
10003 2014-12-12 00:00:00.000 2014-12-12 12:34:00.000
10003 2014-12-12 00:00:00.000 2014-12-12 19:58:00.000
10004 2014-12-12 00:00:00.000 2014-12-12 02:08:00.000
10004 2014-12-12 00:00:00.000 2014-12-12 09:52:00.000
10004 2014-12-12 00:00:00.000 2014-12-12 17:32:00.000
10004 2014-12-12 00:00:00.000 2014-12-12 19:31:00.000
10005 2014-12-12 00:00:00.000 2014-12-12 11:39:00.000
10005 2014-12-12 00:00:00.000 2014-12-12 12:34:00.000
10005 2014-12-12 00:00:00.000 2014-12-12 23:08:00.000
10005 2014-12-12 00:00:00.000 2014-12-12 23:52:00.000
10001 2014-12-13 00:00:00.000 2014-12-13 02:23:00.000
10001 2014-12-13 00:00:00.000 2014-12-13 03:31:00.000
10001 2014-12-13 00:00:00.000 2014-12-13 22:21:00.000
10001 2014-12-13 00:00:00.000 2014-12-13 13:04:00.000
10001 2014-12-14 00:00:00.000 2014-12-14 15:08:00.000
10001 2014-12-14 00:00:00.000 2014-12-14 19:24:00.000
10002 2014-12-13 00:00:00.000 2014-12-13 08:25:00.000
10002 2014-12-14 00:00:00.000 2014-12-14 07:32:00.000
10002 2014-12-14 00:00:00.000 2014-12-14 12:39:07.000
10002 2014-12-14 00:00:00.000 2014-12-14 17:34:00.000
10002 2014-12-14 00:00:00.000 2014-12-14 19:01:00.000
10003 2014-12-13 00:00:00.000 2014-12-13 10:02:00.000
10003 2014-12-13 00:00:00.000 2014-12-13 19:04:00.000
10003 2014-12-13 00:00:00.000 2014-12-13 23:35:00.000
10003 2014-12-14 00:00:00.000 2014-12-14 12:34:00.000
10003 2014-12-14 00:00:00.000 2014-12-14 13:23:00.000
10004 2014-12-13 00:00:00.000 2014-12-13 01:58:00.000
10004 2014-12-13 00:00:00.000 2014-12-13 03:51:00.000
10004 2014-12-13 00:00:00.000 2014-12-13 17:02:00.000
10004 2014-12-14 00:00:00.000 2014-12-14 19:38:00.000
10005 2014-12-13 00:00:00.000 2014-12-13 08:30:00.000
10005 2014-12-13 00:00:00.000 2014-12-13 12:34:00.000
10005 2014-12-13 00:00:00.000 2014-12-13 13:08:00.000
10005 2014-12-13 00:00:00.000 2014-12-13 18:52:00.000

要求输出格式为:

card_no punch_date punch_time1 punch_time2 punch_time3 .. punch_time6                     
10001 2014-12-12 00:00:00.000 2014-12-12 07:59:00.000..(12号数据)
10001 2014-12-13 00:00:00.000 2014-12-13 03:31:00.000..(13号数据)

10002 2014-12-12 00:00:00.000 2014-12-12 ..(12号数据)

...

1005 14最后一天的数据。 

也就是如何把最后一列的数据按时间排成6列,不足6次的设置为空

 

爱如夏花秋叶的主页 爱如夏花秋叶 | 初学一级 | 园豆:192
提问于:2015-01-28 16:22
< >
分享
所有回答(2)
0

写代码完成这个工作,没有什么特别大的难度。

你想要啥,给一个金手指,点一下,然后数据自动排列好?

爱编程的大叔 | 园豆:30813 (高人七级) | 2015-01-28 16:30
0

去了解下pivot

Firen | 园豆:5385 (大侠五级) | 2015-01-28 21:25
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册