请教一个查询

[oo00gg99]

编号 员工 考勤类型 时间
1 1 迟到 2010-09-22
2 2 迟到 2010-09-23
3 1 迟到 2010-09-23
4 2 早退 2010-09-25
5 2 公干 2010-09-26
6 1 公休 2010-09-26
7 2 加班 2010-09-27
8 2 早退 2010-09-27

想要得到一个时间段的统计数据 比如2010-09-01 到2010-09-30 ：
员工 迟到次数 早退次数 公干次数 公休次数 加班次数
1 2 0 0 1 0
2 1 2 0 0 1

请问SQL2000 如何写

[百年树人]

select
员工,
sum(case when 考勤类型='迟到' then 1 else 0 end) as 迟到次数,
sum(case when 考勤类型='早退' then 1 else 0 end) as 早退次数,
sum(case when 考勤类型='公干' then 1 else 0 end) as 公干次数,
sum(case when 考勤类型='公休' then 1 else 0 end) as 公休次数,
sum(case when 考勤类型='加班' then 1 else 0 end) as 加班次数
from tb
group by 员工

[Iovswety]

declare  @sql  varchar(8000) 
set @sql= 'select 员工 ' 
select @sql=@sql+ ',sum(case when 考勤类型 ''' + 考勤类型 + ''' then 1 else 0 end)   as [' + 考勤类型 + ']'   
from (select distinct 考勤类型 from tablename) t   
group by 员工 
exec(@sql) 

[飘零一叶]

IF OBJECT_ID('TB') IS NOT NULL
DROP TABLE TB
GO
CREATE TABLE TB(
编号 int identity(1,1),员工 nvarchar(10),考勤类型 nvarchar(50),时间 datetime
)

insert into TB
select '1','迟到','2010-09-22' union all
select '2','迟到','2010-09-23'union all
select '1','迟到','2010-09-23'union all
select '2','早退','2010-09-25'union all
select '2','公干','2010-09-26'union all
select '1','公休','2010-09-26'union all
select '2','加班','2010-09-27'union all
select '2','早退','2010-09-27'

select  
  员工,
  SUM(case when 考勤类型='迟到' then 1 else 0 end) as 迟到次数,
  SUM(case when 考勤类型='早退' then 1 else 0 end) as 早退次数,
  SUM(case when 考勤类型='公干' then 1 else 0 end) as 公干次数,
  SUM(case when 考勤类型='公休' then 1 else 0 end) as 公休次数,
  SUM(case when 考勤类型='加班' then 1 else 0 end) as 加班次数
from tb 
group by 员工

[Iovswety]

declare  @sql  varchar(8000) 
set @sql= 'select 员工 ' 
select @sql=@sql+ ',sum(case when 考勤类型 ''' + 考勤类型 + ''' then 1 else 0 end)   as [' + 考勤类型 + ']'   
from (select distinct 考勤类型 from tablename) t 
set @sql = @sql + ' from tablename group by 员工'
exec(@sql)