set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



--零售项目综合

ALTER proc [dbo].[P_零售项目综合] 
@yyyymmfrom  int,
@yyyymmto  int
as

begin 
with basetable as

 (SELECT V_人员详细.Staff_Code, V_人员详细.Region_Code, V_人员详细.Sales_Name, V_人员详细.Employee_Name, 'NFABI' AS Project, R_new115NFABI.v, R_new115NFABI.dd
FROM V_人员详细 LEFT JOIN 

    (SELECT  R_new115人员培训信息按月.Employee_Code, R_new115人员培训信息按月.v, R_new115人员培训信息按月.dd
    FROM R_new115人员培训信息按月
    WHERE ((R_new115人员培训信息按月.Training_Class) = 'NFABI Exam-Call Center考试') and dd>=@yyyymmfrom and dd<=@yyyymmto)

R_new115NFABI ON V_人员详细.Staff_Code = R_new115NFABI.Employee_Code
 UNION ALL

SELECT V_人员详细.Staff_Code, V_人员详细.Region_Code, V_人员详细.Sales_Name, V_人员详细.Employee_Name, 'elearning' AS Project, R_new115elearning.v, R_new115elearning.dd
FROM V_人员详细 LEFT JOIN 

    (SELECT  R_new115人员培训信息按月.Employee_Code, R_new115人员培训信息按月.v, R_new115人员培训信息按月.dd
    FROM R_new115人员培训信息按月
    WHERE ((R_new115人员培训信息按月.Training_Class) = 'E-learning Exam') and dd>=@yyyymmfrom and dd<=@yyyymmto)

R_new115elearning ON V_人员详细.Staff_Code = R_new115elearning.Employee_Code
 UNION ALL

SELECT V_人员详细.Staff_Code, V_人员详细.Region_Code, V_人员详细.Sales_Name, V_人员详细.Employee_Name, '销量' AS Project, R_new115销量.v, R_new115销量.dd
FROM V_人员详细 LEFT JOIN 

    (SELECT  Employee_Code, v, dd
    FROM R_new115销量按月
    WHERE dd>=@yyyymmfrom and dd<=@yyyymmto)


R_new115销量 ON V_人员详细.Staff_Code = R_new115销量.Employee_Code
 UNION ALL 

SELECT V_人员详细.Staff_Code, V_人员详细.Region_Code, V_人员详细.Sales_Name, V_人员详细.Employee_Name, '星级' AS Project, R_new115星级.v, R_new115星级.dd
FROM V_人员详细 LEFT JOIN 

    (SELECT  Employee_Code, v, dd
    FROM R_new115星级按月
    WHERE dd>=@yyyymmfrom and dd<=@yyyymmto)


R_new115星级 ON V_人员详细.Staff_Code = R_new115星级.Employee_Code) 

select * into #t from basetable 

declare @sql varchar(max)
set @sql=''  --初始化变量@sql
select @sql=@sql+',['+case when dd is null then '' else dd end +']' from #t group by dd order by dd--变量多值赋值
if @sql=',[]'
select '没有数据'  as '没有数据'
else
begin
SELECT @sql=REPLACE(@sql,',[]','')--去掉null
set @sql=stuff(@sql,1,1,'')--去掉首个','
exec('select * from #t pivot (sum(v) for dd in(' +@sql+ ')) as c')
end

end

--为什么时间不用datetime型非要搞个int？自作孽