USE [Test]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER  procedure [dbo].[GetWorkLoadMain]

@year int,

@month int,

@UserId varchar(50)

as

begin

    declare @day varchar(50)

    set @day=CAST(@year as varchar)+'-'+RIGHT((''+cast(@month as varchar)),2)+'-01'

declare @sql varchar(max)

set @sql=''

select @sql=@sql+',['+Rtrim(ProjectName)+'] int NULL DEFAULT 0  '  from

( select distinct ProjectName from ProRecord  where TypeCode=0 and year(InsertTime)=@year  and month(InsertTime)=@month  ) t

set @sql=stuff( @sql,1,1,'') 

create table  #temp (日期 varchar(50))

    declare @count int

    set @count=(select COUNT(1) from ProRecord  where TypeCode=0 and year(InsertTime)=@year  and month(InsertTime)=@month )

        if @count>0

        begin

        exec (' alter table  #temp   add  '+@sql+'')

        end

insert into #temp(日期)

select convert(varchar(10),dateadd(dd,number,convert(varchar(8),@day,120)+''),120) as time

from master..spt_values

where type='P' and

dateadd(dd,number,convert(varchar(8),@day,120)+'')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,@day),120)+'')

declare @日期  varchar(50)

declare @ProjectName  varchar(50)

declare @num int

declare @ru1 varchar(50)

if(ISNULL(@UserId,'')<>'')

     begin

        declare mycursor cursor for select CONVERT(varchar(10),InsertTime,23) as 日期,ProjectName, count(1) as num from ProRecord where UserId= @UserId and TypeCode=0 and year(InsertTime)=@year  and month(InsertTime)=@month group by CONVERT(varchar(10),InsertTime,23) ,ProjectName

     end

else

     begin

        declare mycursor cursor for select CONVERT(varchar(10),InsertTime,23) as 日期,ProjectName, count(1) as num from ProRecord where TypeCode=0 and year(InsertTime)=@year  and month(InsertTime)=@month group by CONVERT(varchar(10),InsertTime,23) ,ProjectName

     end

open mycursor

fetch next from mycursor into @日期,  @ProjectName,@num

while (@@fetch_status=0)

begin

    set  @ru1 =Rtrim(@ProjectName)

            exec ('update #temp set ['+@ru1+']='+@num+'  where 日期='+''''+@日期+''''+'  ' )

            fetch next from mycursor into  @日期,  @ProjectName, @num

end

close mycursor  

DEALLOCATE mycursor   

end 

declare @sqlnew varchar(8000)

set @sqlnew=''

select @sqlnew=@sqlnew+',sum(cast('+name+' as int)) as '+name from tempdb.dbo.syscolumns where id=object_id('tempdb.dbo.#temp') and name not in('日期') order by colid     --写不要汇总的列名

exec ('select * from #temp  where 1=1  union all select ''合计'' as 日期'+@sqlnew+' from #temp  ')

--exec [GetWorkLoadMain] 2017,6,''

--exec [GetWorkLoadMain] 2017,6,'admin'

declare @sql varchar(max)

set @sql=''

select @sql=@sql+',['+Rtrim(ProjectName)+'] int NULL DEFAULT 0 ' from

( select distinct ProjectName from ProRecord where TypeCode=0 and year(InsertTime)=@year and month(InsertTime)=@month ) t

set @sql=stuff( @sql,1,1,'')

create table #temp (日期 varchar(50))

exec (' alter table #temp add '+@sql+'')

select convert(varchar(10),dateadd(dd,number,convert(varchar(8),'2017-06-01',120)+''),120) as time

from master..spt_values

where type='P' and

dateadd(dd,number,convert(varchar(8),'2017-06-01',120)+'')<=dateadd(dd,-1,convert(varchar(8),dateadd(mm,1,'2017-06-01'),120)+'')

declare @日期 varchar(50)

declare @ProjectName varchar(50)

declare @num int

declare @ru1 varchar(50)

declare mycursor cursor for select CONVERT(varchar(10),InsertTime,23) as 日期,ProjectName, count(1) as num from ProRecord where UserId= @UserId and TypeCode=0 and year(InsertTime)=@year and month(InsertTime)=@month group by CONVERT(varchar(10),InsertTime,23) ,ProjectName

open mycursor

fetch next from mycursor into @日期, @ProjectName,@num

while (@@fetch_status=0)

begin

set @ru1 =Rtrim(@ProjectName)

exec ('update #temp set ['+@ru1+']='+@num+' where 日期='+''''+@日期+''''+' ' )

fetch next from mycursor into @日期, @ProjectName, @num

end

close mycursor

DEALLOCATE mycursor

end

declare @sqlnew varchar(8000)

set @sqlnew=''

select @sqlnew=@sqlnew+',sum('+name+' )  as '+name from tempdb.dbo.syscolumns where id=object_id('tempdb.dbo.#temp') and name not in('日期') order by colid --写不要汇总的列名

exec ('select ''合计'' as 日期'+@sqlnew+' from #temp ')