在系统有一记录员工打卡信息的表,其中EmpNo为员工号,AttDate为出勤日期,SeqNo为班段序号,TimeIn为上班打卡的时间,TimeOut为下班打卡的时间,CQ为迟到,ZT为早退 ,其中一人一天的班段(即是SeqNo)可能有多个,这个数是不固定的,EmpNo AttDate SeqNo TimeIn TimeOut CQ ZT
A001 2007-10-10 1 8:05 12:05 5 0
A001 2007-10-10 2 13:10 17:31 0 0
A002 2007-10-10 1 7:55 11:56 0 4
A002 2007-10-10 2 13:15 17:30 0 0
A002 2007-10-10 3 18:15 20:30 0 0
A001 2007-10-11 1 8:02 12:06 2 0
A001 2007-10-11 2 13:12 17:31 0 0
A001 2007-10-11 3 18:18 20:32 0 0
请问将上述数据行列转换后的结果是如下(若SeqNo有4,则在下面的数据中有TimeIn4和TimeOut4)
EmpNo AttDate timeIn1 timeOut1 CQ1 ZT1 timeIn2 timeOut2 CQ2 ZT2 timeIn3 timeOut3 CQ3 ZT3
A001 2007-10-10 8:05 12:05 5 0 13:10 17:31 0 0 0 0
A002 2007-10-10 7:55 11:56 0 4 13:15 17:30 0 0 18:15 20:30 0 0
A001 2007-10-11 8:02 12:06 2 0 13:12 17:31 0 0 18:18 20:32 0 0
谢谢大侠指点,请问有几种实现方法?
A001 2007-10-10 1 8:05 12:05 5 0
A001 2007-10-10 2 13:10 17:31 0 0
A002 2007-10-10 1 7:55 11:56 0 4
A002 2007-10-10 2 13:15 17:30 0 0
A002 2007-10-10 3 18:15 20:30 0 0
A001 2007-10-11 1 8:02 12:06 2 0
A001 2007-10-11 2 13:12 17:31 0 0
A001 2007-10-11 3 18:18 20:32 0 0
请问将上述数据行列转换后的结果是如下(若SeqNo有4,则在下面的数据中有TimeIn4和TimeOut4)
EmpNo AttDate timeIn1 timeOut1 CQ1 ZT1 timeIn2 timeOut2 CQ2 ZT2 timeIn3 timeOut3 CQ3 ZT3
A001 2007-10-10 8:05 12:05 5 0 13:10 17:31 0 0 0 0
A002 2007-10-10 7:55 11:56 0 4 13:15 17:30 0 0 18:15 20:30 0 0
A001 2007-10-11 8:02 12:06 2 0 13:12 17:31 0 0 18:18 20:32 0 0
谢谢大侠指点,请问有几种实现方法?
insert into test values('A001','2007-10-10',1,'08:05','12:05',5,0)
insert into test values('A001','2007-10-10',2,'13:10','17:31',0,0)
insert into test values('A002','2007-10-10',1,'07:55','11:56',0,4)
insert into test values('A002','2007-10-10',2,'13:15','17:30',0,0)
insert into test values('A002','2007-10-10',3,'18:15','20:30',0,0)
insert into test values('A001','2007-10-11',1,'08:02','12:06',2,0)
insert into test values('A001','2007-10-11',2,'13:12','17:31',0,0)
insert into test values('A001','2007-10-11',3,'18:18','20:32',0,0)
godeclare @sql varchar(8000)
set @sql=''select @sql=@sql+',[timeIn'+rtrim(SeqNo)+']=max(case SeqNo when '+rtrim(SeqNo)+' then TimeIn end)'
+',[TimeOut'+rtrim(SeqNo)+']=max(case SeqNo when '+rtrim(SeqNo)+' then TimeOut end)'
+',[CQ'+rtrim(SeqNo)+']=sum(case SeqNo when '+rtrim(SeqNo)+' then CQ else 0 end)'
+',[ZT'+rtrim(SeqNo)+']=sum(case SeqNo when '+rtrim(SeqNo)+' then ZT else 0 end)'
from (select distinct SeqNo from test) tset @sql='create view t_view as select EmpNo,AttDate'+@sql+' from test group by EmpNo,AttDate'exec(@sql)select * from t_view order by EmpNo,AttDate
go
/*
EmpNo AttDate timeIn1 TimeOut1 CQ1 ZT1 timeIn2 TimeOut2 CQ2 ZT2 timeIn3 TimeOut3 CQ3 ZT3
---------- ---------- -------- -------- ----------- ----------- -------- -------- ----------- ----------- -------- -------- ----------- -----------
A001 2007-10-10 08:05 12:05 5 0 13:10 17:31 0 0 NULL NULL 0 0
A001 2007-10-11 08:02 12:06 2 0 13:12 17:31 0 0 18:18 20:32 0 0
A002 2007-10-10 07:55 11:56 0 4 13:15 17:30 0 0 18:15 20:30 0 0
*/drop view t_view
drop table test
go