求教：自动生成起始日期列，按日期位置显示任务
1）项目计划包括1）开始日期，2）结束日期，3）人员，4）任务描述
2012-04-01 2012-04-6 甲 任务1
2012-04-02 2012-04-11 乙 任务2
2012-04-7 2012-04-10 甲 任务3
2012-04-01 2012-04-3 丙 任务4
2）请问如何获得：
甲 乙 丙
2012-04-01 任务1 任务4
2012-04-02 任务3
2012-04-03
2012-04-04
2012-04-05
2012-04-06
2012-04-07 任务2
2012-04-08
2012-04-09
2012-04-10
2012-04-11
[解决办法]
with t(开始时间,结束时间,人员,任务描述) as (
select CAST('2012-04-01' as DATE), CAST('2012-04-06' as DATE), '甲', '任务1'
union all select CAST('2012-04-02' as DATE), CAST('2012-04-11' as DATE), '乙', '任务2'
union all select CAST('2012-04-07' as DATE), CAST('2012-04-10' as DATE), '甲', '任务3'
union all select CAST('2012-04-01' as DATE), CAST('2012-04-03' as DATE), '丙', '任务4'
)
,t1 as (select 开始时间 D from t union select 结束时间 from t)
,t2 as (select MIN(D) minD,Max(D) maxD from t1)
,diffD as( select number
from master..spt_values where type='P' and number between 0
and (select DATEDIFF(DD,minD,maxD) from t2)
),dates as (
select cast(DATEADD(dd,number,(select minD from t2)) as DATE) as [date]
from diffD
), src as (
select [date],人员,任务描述
from dates d
left join t on d.[date]=t.开始时间)
select * from src
pivot(max(任务描述) for 人员 in ([甲],[乙],[丙])) pvt
[解决办法]
create table 项目计划(开始日期 date,结束日期 date,人员 varchar(4),任务描述 varchar(8))insert into 项目计划select '2012-04-01', '2012-04-6', '甲', '任务1' union allselect '2012-04-02', '2012-04-11', '乙', '任务2' union allselect '2012-04-07', '2012-04-10', '甲', '任务3' union allselect '2012-04-01', '2012-04-3', '丙', '任务4'declare @sql varchar(6000),@pl varchar(2000)select @pl=stuff((select ',isnull(['+人员+'],'''') ['+人员+']' from(select distinct 人员 from 项目计划) torder by case 人员 when '甲' then 1 when '乙' then 2when '丙' then 3 endfor 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) dlfrom t1inner join master.dbo.spt_values bon b.[type]=''P'' and b.number<=t1.ds),t3 as(select 开始日期,'+@pl+'from (select 开始日期,人员,任务描述 from 项目计划) apivot(max(任务描述) for 人员 in([甲],[乙],[丙])) t)select t2.dl ''开始日期'','+@pl+'from t2left join t3 on t2.dl=t3.开始日期'exec(@sql)开始日期 甲 乙 丙---------- -------- -------- --------2012-04-01 任务1 任务42012-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)