考勤表：
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
------解决方案--------------------
适合sql serve 2000：
create table 考勤表(carddate datetime, empno varchar(10),cardtime2 varchar(10))
insert into 考勤表
select '2014-02-12 00:00:00', 'A012152', '13:23:16' union all
select '2014-02-12 00:00:00', 'A012152', '12:04:01' union all
select '2014-02-12 00:00:00', 'A012152', '17:35:21' union all
select '2014-02-12 00:00:00', 'A012152', '07:45:56' union all
select '2014-02-11 00:00:00', 'A012152', '13:19:18' union all
select '2014-02-11 00:00:00', 'A012152', '12:03:26' union all
select '2014-02-11 00:00:00', 'A012152', '07:44:19' union all
select '2014-02-11 00:00:00', 'A012152', '17:35:22' union all
select '2014-02-10 00:00:00', 'A012152', '17:34:14' union all
select '2014-02-10 00:00:00', 'A012152', '13:22:35' union all
select '2014-02-10 00:00:00', 'A012152', '12:02:54' union all
select '2014-02-10 00:00:00', 'A012152', '07:44:33'
go
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
select carddate,
empno,
MAX(case when rownum = 1 then cardtime2 else null end) as cardtime1,
MAX(case when rownum = 2 then cardtime2 else null end) as cardtime1,
MAX(case when rownum = 3 then cardtime2 else null end) as cardtime1,
MAX(case when rownum = 4 then cardtime2 else null end) as cardtime1
from #temp1
group by carddate,empno
order by empno,carddate desc
------解决方案--------------------
这个适合2000，同时能适应不同empno，不同carddate，cardtime2的条数不同的情况，动态语句：
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