drop table tablelist
go
create table tablelist(日期 datetime,早班上班刷卡时间 datetime,早班下班刷卡时间 datetime,中班上班刷卡时间 datetime,中班下班刷卡时间 datetime)
insert into tablelist
select '2006-04-01','2006-04-01 7:21:00','2006-04-01 11:30:00','2006-04-01 13:59:00','2006-04-01 18:00:00'
union all select '2006-04-02','2006-04-02 7:30:00','2006-04-02 12:00:00','2006-04-02 14:01:00','2006-04-02 18:03:00'
union all select '2006-04-03','2006-04-03 7:14:00','2006-04-03 11:45:00','2006-04-03 13:56:00','2006-04-03 17:59:00'
declare @sql varchar(8000),@sql1 varchar(2000),@sql2 varchar(2000),@sql3 varchar(2000),@sql4 varchar(2000)
select @sql1='',@sql2='',@sql3='',@sql4=''
select @sql1=@sql1+',max(case when convert(varchar(10),早班上班刷卡时间,120) ='''+日期+''' then right(convert(varchar(19),早班上班刷卡时间,120),8) else ''-'' end) as '''+日期+''''
from (select distinct convert(char(10),日期,120) as 日期 from tablelist)tablelist
select @sql2=@sql2+',max(case when convert(varchar(10),早班下班刷卡时间,120) ='''+日期+''' then right(convert(varchar(19),早班下班刷卡时间,120),8) else ''-'' end)'
from (select distinct convert(char(10),日期,120) as 日期 from tablelist)tablelist
select @sql3=@sql3+',max(case when convert(varchar(10),中班上班刷卡时间,120) ='''+日期+''' then right(convert(varchar(19),中班上班刷卡时间,120),8) else ''-'' end)'
from (select distinct convert(char(10),日期,120) as 日期 from tablelist)tablelist
select @sql4=@sql4+',max(case when convert(varchar(10),中班下班刷卡时间,120) ='''+日期+''' then right(convert(varchar(19),中班下班刷卡时间,120),8) else ''-'' end)'
from (select distinct convert(char(10),日期,120) as 日期 from tablelist)tablelist
set @sql='select ''早班上班刷卡时间'' as type'+@sql1+' from tablelist'+char(13)+'union all'+char(13)+'select ''早班下班刷卡时间'''+@sql2+' from tablelist'+char(13)+'union all'+char(13)+'select ''中班上班刷卡时间'''+@sql3+' from tablelist'+char(13)+'union all'+char(13)+'select ''中班下班刷卡时间'''+@sql4+' from tablelist'
exec(@sql)