所以,觉得有必要记录下来,这样以后再次碰到这类问题,也能从中获取解答的思路。
http://bbs.csdn.net/topics/390709333
sql server 2000,考勤表:
carddate empno cardtime2
2014-02-12 00:00:00 A012152 13:23:16
2014-02-12 00:00:00 A012152 12:04:01
2014-02-12 00:00:00 A012152 17:35:21
2014-02-12 00:00:00 A012152 07:45:56
2014-02-11 00:00:00 A012152 13:19:18
2014-02-11 00:00:00 A012152 12:03:26
2014-02-11 00:00:00 A012152 07:44:19
2014-02-11 00:00:00 A012152 17:35:22
2014-02-10 00:00:00 A012152 17:34:14
2014-02-10 00:00:00 A012152 13:22:35
2014-02-10 00:00:00 A012152 12:02:54
2014-02-10 00:00:00 A012152 07:44:33
需要数据
carddate empno cardtime1 cardtime1 cardtime1 cardtime1
2014-02-12 00:00:00 A012152 13:23:16 12:04:01 17:35:21 07:45:56
2014-02-11 00:00:00 A012152 13:19:18 12:03:26 07:44:19 17:35:22
2014-02-10 00:00:00 A012152 17:34:14 13:22:35 12:02:54 07:44:33
if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
if OBJECT_ID('tempdb..#temp1') is not null
drop table #temp1
select *,identity(int,1,1) as id into #temp
from 考勤表
select *,
(select COUNT(*) from #temp b
where a.empno = b.empno and a.carddate = b.carddate and a.id >= b.id) rownum
into #temp1
from #temp a
declare @sql nvarchar(max);
set @sql = '';
select
@sql = @sql + ',max(case when rownum = '+cast(rownum as varchar)+' then cardtime2 else null end) as cardtime'+ cast(rownum as varchar)
from #temp1
group by rownum
select @sql = 'select carddate,empno' + @sql +
' from #temp1' +
' group by carddate,empno
order by empno,carddate desc'
--select @sql
exec(@sql)
/*
carddate empno cardtime1 cardtime2 cardtime3 cardtime4
2014-02-12 00:00:00.000 A012152 13:23:16 12:04:01 17:35:21 07:45:56
2014-02-11 00:00:00.000 A012152 13:19:18 12:03:26 07:44:19 17:35:22
2014-02-10 00:00:00.000 A012152 17:34:14 13:22:35 12:02:54 07:44:33
*/