关于交叉表的应用于 考勤系统解决方法

关于交叉表的应用于 考勤系统
workid recdate rectime
------ ------- --------
02109 2012-3-1 07:36
02109 2012-3-1 09:36
02109 2012-3-1 17:36
02103 2012-3-1 07:36
02103 2012-3-1 17:36
02109 2012-3-2 07:38

02102 2012-3-2 07:36


…………
如何 变成 

workid 2012-3-1 2012-3-2 ………后面一直到31日…
------ ---- ---------
02109 07:36 07:38
02109 09:36
02109 17:36
02103 07:36
02103 17:36
02102 07:36

每个workid 每天可能有几个记录 需都显示出来

如何写sql

[解决办法]

SQL code

--> 测试数据：[tbl]if object_id('[tbl]') is not null drop table [tbl]create table [tbl]([workid] varchar(5),[recdate] date,[rectime] time)insert into tblselect '02109','2012-3-1','07:36' union allselect '02109','2012-3-1','09:36' union allselect '02109','2012-3-1','17:36' union allselect '02103','2012-3-1','07:36' union allselect '02103','2012-3-1','17:36' union allselect '02109','2012-3-2','07:38' union allselect '02102','2012-3-2','07:36'declare @str varchar(max)set @str=''select @str=@str+','+QUOTENAME([recdate],'')+'=case when [recdate]='+QUOTENAME([recdate],'''')+' then [rectime] else  null end' from tbl group by [recdate]exec('select [workid]'+@str+'  from tbl')print @str/*workid    2012-03-01    2012-03-0202109    07:36:00.0000000    NULL02109    09:36:00.0000000    NULL02109    17:36:00.0000000    NULL02103    07:36:00.0000000    NULL02103    17:36:00.0000000    NULL02109    NULL    07:38:00.000000002102    NULL    07:36:00.0000000*/
[解决办法]
探讨

感谢楼上 支持 但这个结果并非是我要的结果

就02109 而言 2012-3-1 有3条记录 是需要都显示出来的