打卡计时表格
打卡计时报表
SQL2000,，代码如下：
里面的#a表数据如下：
days daytime cardno staffname
2014-1-2 2014-1-2 8:48 922533 Adam Wan
2014-1-2 2014-1-2 20:14 922533 Adam Wan
2014-1-3 2014-1-3 8:40 922533 Adam Wan
2014-1-3 2014-1-3 21:42 922533 Adam Wan
2014-1-4 2014-1-4 8:44 922533 Adam Wan
2014-1-4 2014-1-4 20:25 922533 Adam Wan
2014-1-6 2014-1-6 8:53 922533 Adam Wan
2014-1-6 2014-1-6 20:48 922533 Adam Wan
2014-1-9 2014-1-9 9:00 922533 Adam Wan
2014-1-9 2014-1-9 20:54 922533 Adam Wan
2014-1-10 2014-1-10 9:02 922533 Adam Wan
2014-1-10 2014-1-10 20:23 922533 Adam Wan
2014-1-11 2014-1-11 9:02 922533 Adam Wan
2014-1-11 2014-1-11 13:57 922533 Adam Wan
2014-1-13 2014-1-13 9:07 922533 Adam Wan
2014-1-13 2014-1-13 19:51 922533 Adam Wan
2014-1-17 2014-1-17 8:50 922533 Adam Wan
2014-1-17 2014-1-17 20:55 922533 Adam Wan
2014-1-20 2014-1-20 8:59 922533 Adam Wan
2014-1-20 2014-1-20 21:08 922533 Adam Wan
2014-1-21 2014-1-21 14:03 922533 Adam Wan
2014-1-21 2014-1-21 21:23 922533 Adam Wan
2014-1-24 2014-1-24 9:29 922533 Adam Wan
2014-1-24 2014-1-24 20:12 922533 Adam Wan
2014-1-25 2014-1-25 9:03 922533 Adam Wan
2014-1-25 2014-1-25 13:49 922533 Adam Wan
2014-1-27 2014-1-27 8:59 922533 Adam Wan
2014-1-27 2014-1-27 20:02 922533 Adam Wan
2014-1-28 2014-1-28 8:49 922533 Adam Wan
2014-1-28 2014-1-28 17:57 922533 Adam Wan
2014-1-9 2014-1-9 9:04 511844 Aggie Siu
2014-1-9 2014-1-9 18:43 511844 Aggie Siu
2014-1-10 2014-1-10 8:59 511844 Aggie Siu
2014-1-10 2014-1-10 18:49 511844 Aggie Siu
2014-1-11 2014-1-11 8:49 511844 Aggie Siu
2014-1-11 2014-1-11 12:52 511844 Aggie Siu
2014-1-13 2014-1-13 9:07 511844 Aggie Siu
2014-1-13 2014-1-13 19:15 511844 Aggie Siu
2014-1-14 2014-1-14 9:00 511844 Aggie Siu
2014-1-14 2014-1-14 19:20 511844 Aggie Siu
2014-1-15 2014-1-15 8:58 511844 Aggie Siu
2014-1-15 2014-1-15 13:26 511844 Aggie Siu
2014-1-16 2014-1-16 8:59 511844 Aggie Siu
2014-1-16 2014-1-16 19:19 511844 Aggie Siu
2014-1-17 2014-1-17 9:01 511844 Aggie Siu
2014-1-17 2014-1-17 21:40 511844 Aggie Siu
2014-1-20 2014-1-20 9:00 511844 Aggie Siu
2014-1-20 2014-1-20 21:06 511844 Aggie Siu
2014-1-23 2014-1-23 9:05 511844 Aggie Siu
2014-1-23 2014-1-23 20:24 511844 Aggie Siu
2014-1-24 2014-1-24 9:11 511844 Aggie Siu
2014-1-24 2014-1-24 21:17 511844 Aggie Siu
2014-1-25 2014-1-25 8:50 511844 Aggie Siu
2014-1-25 2014-1-25 13:54 511844 Aggie Siu
2014-1-27 2014-1-27 8:57 511844 Aggie Siu
2014-1-27 2014-1-27 19:10 511844 Aggie Siu
2014-1-28 2014-1-28 9:03 511844 Aggie Siu
2014-1-28 2014-1-28 18:39 511844 Aggie Siu
2014-1-6 2014-1-6 19:31 511844 Aggie Siu
2014-1-6 2014-1-6 9:01 511844 Aggie Siu
2014-1-4 2014-1-4 13:28 511844 Aggie Siu
2014-1-3 2014-1-3 21:01 511844 Aggie Siu
2014-1-3 2014-1-3 9:00 511844 Aggie Siu
2014-1-2 2014-1-2 20:14 511844 Aggie Siu
2014-1-2 2014-1-2 9:01 511844 Aggie Siu
2014-1-7 2014-1-7 8:50 803514 Amandan Lo
2014-1-7 2014-1-7 18:30 803514 Amandan Lo
2014-1-8 2014-1-8 8:48 803514 Amandan Lo
2014-1-17 2014-1-17 8:48 803514 Amandan Lo
2014-1-17 2014-1-17 18:16 803514 Amandan Lo
2014-1-20 2014-1-20 8:52 803514 Amandan Lo
2014-1-20 2014-1-20 18:17 803514 Amandan Lo
2014-1-21 2014-1-21 8:52 803514 Amandan Lo
2014-1-21 2014-1-21 19:04 803514 Amandan Lo
2014-1-22 2014-1-22 8:49 803514 Amandan Lo
2014-1-22 2014-1-22 18:08 803514 Amandan Lo
2014-1-23 2014-1-23 8:52 803514 Amandan Lo
...... 省略了几百行。然后我需要将这个表二维化，将cardno,staffname摆最左边，然后将days打横，向右边延伸，接着把daytime摆进右边的格子。
但是得出的结果却是如下：
cardno staffname 2014-1-1 2014-1-2 2014-1-3 2014-1-4 2014-1-5 2014-1-6 2014-1-7 2014-1-8 2014-1-9 2014-1-10 2014-1-11
SQL2000,，代码如下：
ALTER Procedure HKTimeCard_Report @Day Varchar(12)
As
if Exists (Select * From Tempdb.dbo.sysobjects where name='#a') Drop Table #a
if Exists (Select * From Tempdb.dbo.sysobjects where name='#b') Drop Table #b
Select
Convert(Varchar(10),a.Daytime,120) Days,
Convert(Varchar(10),a.Daytime,120)+' '+SubString(Convert(Varchar(10),a.Daytime,108),1,5) Daytime,
a.CARDNO,b.StaffName
into #a
From
HKTimeCardData a inner join HKTimeCardStaff b on a.CARDNO=b.CARDNO
where
a.Status=1
Order by
b.StaffName
Select * From #a
declare @sql varchar(8000)
set @sql='select CARDNO,StaffName'
select @sql=@sql+',['+dd+']=min(case Days when '''+dd+''' then Daytime else '''' end)'
from (select distinct dd=Days from #a ) ss
set @sql=@sql+' from #a group by CARDNO,StaffName Order by CARDNO '
exec(@sql)
/*
HKTimeCard_Report '2014-01'
*/
里面的#a表数据如下：
days daytime cardno staffname
2014-1-2 2014-1-2 8:48 922533 Adam Wan
2014-1-2 2014-1-2 20:14 922533 Adam Wan
2014-1-3 2014-1-3 8:40 922533 Adam Wan
2014-1-3 2014-1-3 21:42 922533 Adam Wan
2014-1-4 2014-1-4 8:44 922533 Adam Wan
2014-1-4 2014-1-4 20:25 922533 Adam Wan
2014-1-6 2014-1-6 8:53 922533 Adam Wan
2014-1-6 2014-1-6 20:48 922533 Adam Wan
2014-1-9 2014-1-9 9:00 922533 Adam Wan
2014-1-9 2014-1-9 20:54 922533 Adam Wan
2014-1-10 2014-1-10 9:02 922533 Adam Wan
2014-1-10 2014-1-10 20:23 922533 Adam Wan
2014-1-11 2014-1-11 9:02 922533 Adam Wan
2014-1-11 2014-1-11 13:57 922533 Adam Wan
2014-1-13 2014-1-13 9:07 922533 Adam Wan
2014-1-13 2014-1-13 19:51 922533 Adam Wan
2014-1-17 2014-1-17 8:50 922533 Adam Wan
2014-1-17 2014-1-17 20:55 922533 Adam Wan
2014-1-20 2014-1-20 8:59 922533 Adam Wan
2014-1-20 2014-1-20 21:08 922533 Adam Wan
2014-1-21 2014-1-21 14:03 922533 Adam Wan
2014-1-21 2014-1-21 21:23 922533 Adam Wan
2014-1-24 2014-1-24 9:29 922533 Adam Wan
2014-1-24 2014-1-24 20:12 922533 Adam Wan
2014-1-25 2014-1-25 9:03 922533 Adam Wan
2014-1-25 2014-1-25 13:49 922533 Adam Wan
2014-1-27 2014-1-27 8:59 922533 Adam Wan
2014-1-27 2014-1-27 20:02 922533 Adam Wan
2014-1-28 2014-1-28 8:49 922533 Adam Wan
2014-1-28 2014-1-28 17:57 922533 Adam Wan
2014-1-9 2014-1-9 9:04 511844 Aggie Siu
2014-1-9 2014-1-9 18:43 511844 Aggie Siu
2014-1-10 2014-1-10 8:59 511844 Aggie Siu
2014-1-10 2014-1-10 18:49 511844 Aggie Siu
2014-1-11 2014-1-11 8:49 511844 Aggie Siu
2014-1-11 2014-1-11 12:52 511844 Aggie Siu
2014-1-13 2014-1-13 9:07 511844 Aggie Siu
2014-1-13 2014-1-13 19:15 511844 Aggie Siu
2014-1-14 2014-1-14 9:00 511844 Aggie Siu
2014-1-14 2014-1-14 19:20 511844 Aggie Siu
2014-1-15 2014-1-15 8:58 511844 Aggie Siu
2014-1-15 2014-1-15 13:26 511844 Aggie Siu
2014-1-16 2014-1-16 8:59 511844 Aggie Siu
2014-1-16 2014-1-16 19:19 511844 Aggie Siu
2014-1-17 2014-1-17 9:01 511844 Aggie Siu
2014-1-17 2014-1-17 21:40 511844 Aggie Siu
2014-1-20 2014-1-20 9:00 511844 Aggie Siu
2014-1-20 2014-1-20 21:06 511844 Aggie Siu
2014-1-23 2014-1-23 9:05 511844 Aggie Siu
2014-1-23 2014-1-23 20:24 511844 Aggie Siu
2014-1-24 2014-1-24 9:11 511844 Aggie Siu
2014-1-24 2014-1-24 21:17 511844 Aggie Siu
2014-1-25 2014-1-25 8:50 511844 Aggie Siu
2014-1-25 2014-1-25 13:54 511844 Aggie Siu
2014-1-27 2014-1-27 8:57 511844 Aggie Siu
2014-1-27 2014-1-27 19:10 511844 Aggie Siu
2014-1-28 2014-1-28 9:03 511844 Aggie Siu
2014-1-28 2014-1-28 18:39 511844 Aggie Siu
2014-1-6 2014-1-6 19:31 511844 Aggie Siu
2014-1-6 2014-1-6 9:01 511844 Aggie Siu
2014-1-4 2014-1-4 13:28 511844 Aggie Siu
2014-1-3 2014-1-3 21:01 511844 Aggie Siu
2014-1-3 2014-1-3 9:00 511844 Aggie Siu
2014-1-2 2014-1-2 20:14 511844 Aggie Siu
2014-1-2 2014-1-2 9:01 511844 Aggie Siu
2014-1-7 2014-1-7 8:50 803514 Amandan Lo
2014-1-7 2014-1-7 18:30 803514 Amandan Lo
2014-1-8 2014-1-8 8:48 803514 Amandan Lo
2014-1-17 2014-1-17 8:48 803514 Amandan Lo
2014-1-17 2014-1-17 18:16 803514 Amandan Lo
2014-1-20 2014-1-20 8:52 803514 Amandan Lo
2014-1-20 2014-1-20 18:17 803514 Amandan Lo
2014-1-21 2014-1-21 8:52 803514 Amandan Lo
2014-1-21 2014-1-21 19:04 803514 Amandan Lo
2014-1-22 2014-1-22 8:49 803514 Amandan Lo
2014-1-22 2014-1-22 18:08 803514 Amandan Lo
2014-1-23 2014-1-23 8:52 803514 Amandan Lo
...... 省略了几百行。然后我需要将这个表二维化，将cardno,staffname摆最左边，然后将days打横，向右边延伸，接着把daytime摆进右边的格子。
但是得出的结果却是如下：
cardno staffname 2014-1-1 2014-1-2 2014-1-3 2014-1-4 2014-1-5 2014-1-6 2014-1-7 2014-1-8 2014-1-9 2014-1-10 2014-1-11