create table 项目计划
(开始日期 date,结束日期 date,人员 varchar(4),任务描述 varchar(8))

insert into 项目计划
select '2012-04-01', '2012-04-6', '甲', '任务1' union all
select '2012-04-02', '2012-04-11', '乙', '任务2' union all
select '2012-04-07', '2012-04-10', '甲', '任务3' union all
select '2012-04-01', '2012-04-3', '丙', '任务4'


declare @sql varchar(6000),@pl varchar(2000)

select @pl=stuff(
(select ',isnull(['+人员+'],'''') ['+人员+']' from
(select distinct 人员 from 项目计划) t
order by case 人员 
when '甲' then 1  
when '乙' then 2
when '丙' then 3 end
for xml path('')),1,1,'')

select @sql=
'with t1 as
(select min(开始日期) mb,
        datediff(d,min(开始日期),max(结束日期)) ds 
 from 项目计划),
t2 as
(select dateadd(d,b.number,t1.mb) dl
from t1
inner join master.dbo.spt_values b
on b.[type]=''P'' and b.number<=t1.ds),
t3 as
(select 开始日期,'+@pl+'
from (select 开始日期,人员,任务描述 from 项目计划) a
pivot(max(任务描述) for 人员 in([甲],[乙],[丙])) t)
select t2.dl ''开始日期'','+@pl+'
from t2
left join t3 on t2.dl=t3.开始日期'

exec(@sql)


开始日期       甲        乙        丙
---------- -------- -------- --------
2012-04-01 任务1               任务4
2012-04-02          任务2      
2012-04-03                   
2012-04-04                   
2012-04-05                   
2012-04-06                   
2012-04-07 任务3               
2012-04-08                   
2012-04-09                   
2012-04-10                   
2012-04-11                   

(11 row(s) affected)