行转列写的一个时间看板

declare @Client varchar(50);
set @Client ='-1'; --lxl

declare @strtimeList nvarchar(max);
set @strtimeList='';
declare @titlList nvarchar(800);
set @titlList='';
declare @strsql nvarchar(max);
set @strsql=N'';
declare @tbtime table
(id int identity(1,1),
strtime nvarchar(50)
);
create table #tb
(strtime nvarchar(100),
proportion float,
chrproportion varchar(100),
strtype  nvarchar(50)
);

SET DATEFIRST 1
declare @CurYear int;
select @CurYear=datepart(year,GETDATE());
DECLARE @CurMonth INT; --第几月
select @CurMonth=datepart(month,GETDATE());
DECLARE @CurWeek INT;  --第几星期
SELECT @CurWeek=DATEPART(Week,GETDATE());
DECLARE @CurDay INT; --第几天
SELECT @CurDay=DATEPART(WeekDay,GETDATE());
--select @CurDay;--lxl
declare @strnowmouth varchar(50); -- 获取这个月的一号时间
set @strnowmouth=cast(@CurYear as varchar(4))+'-'+cast(@CurMonth as varchar(2))+'-1';
declare @monthweek int; --获取这个月1号的第几周
set @monthweek=datepart(Week,cast(@strnowmouth as datetime));
declare @daynumer int;--获取今天几号
set @daynumer=datepart(Dd,GETDATE());
--select @daynumer; --lxl

if(@Client<>'-1')  --获取某种客户的统计数据
begin
 --添加月的统计数据
 insert into #tb(strtime,proportion,strtype) select (cast(datepart(month,a.ScanDate) as varchar(50))+N'月') as Whichmonth,case sum(a.TheoryUsed+a.FGRTVUsed+a.FGRTCUesd+a.FGScrapUesd
  +a.MaterialRTV+a.MaterialRTC+a.MaterialScrap) when 0 then 0 else (sum(a.IssueQty-a.TheoryUsed-a.FGRTVUsed-a.FGRTCUesd-a.FGScrapUesd
  -a.MaterialRTV-a.MaterialRTC-a.MaterialScrap-a.ReturnQty-a.ThrowMaterialQty)/sum(a.TheoryUsed+a.FGRTVUsed+a.FGRTCUesd+a.FGScrapUesd
  +a.MaterialRTV+a.MaterialRTC+a.MaterialScrap))*100 end as proportion,b.Type  from dbo.ModelLossInfo a left join dbo.PNInformation b
  on a.PN=b.PN
  where datepart(month,a.ScanDate)<=@CurMonth and b.Client=@Client group by datepart(month,a.ScanDate),b.Type;

 --添加周的统计数据
 insert into #tb(strtime,proportion,strtype) select (N'week'+cast(datepart(Week,a.ScanDate) as varchar(50))) as Whichmonth,case sum(a.TheoryUsed+a.FGRTVUsed+a.FGRTCUesd+a.FGScrapUesd
  +a.MaterialRTV+a.MaterialRTC+a.MaterialScrap) when 0 then 0 else (sum(a.IssueQty-a.TheoryUsed-a.FGRTVUsed-a.FGRTCUesd-a.FGScrapUesd
  -a.MaterialRTV-a.MaterialRTC-a.MaterialScrap-a.ReturnQty-a.ThrowMaterialQty)/sum(a.TheoryUsed+a.FGRTVUsed+a.FGRTCUesd+a.FGScrapUesd
  +a.MaterialRTV+a.MaterialRTC+a.MaterialScrap))*100 end as proportion,b.Type from dbo.ModelLossInfo a left join dbo.PNInformation b
  on a.PN=b.PN
  where datepart(month,a.ScanDate)=@CurMonth and DATEPART(Week,a.ScanDate)<=@CurWeek and b.Client=@Client group by datepart(Week,a.ScanDate),b.Type;
 
 --添加天的统计数据
 insert into #tb(strtime,proportion,strtype) select (cast((@daynumer-(@CurDay-datepart(WeekDay,a.ScanDate))) as varchar(50))+N'号') as Whichmonth,case sum(a.TheoryUsed+a.FGRTVUsed+a.FGRTCUesd+a.FGScrapUesd
  +a.MaterialRTV+a.MaterialRTC+a.MaterialScrap) when 0 then 0 else (sum(a.IssueQty-a.TheoryUsed-a.FGRTVUsed-a.FGRTCUesd-a.FGScrapUesd
  -a.MaterialRTV-a.MaterialRTC-a.MaterialScrap-a.ReturnQty-a.ThrowMaterialQty)/sum(a.TheoryUsed+a.FGRTVUsed+a.FGRTCUesd+a.FGScrapUesd
  +a.MaterialRTV+a.MaterialRTC+a.MaterialScrap))*100 end as proportion,b.Type from dbo.ModelLossInfo a left join dbo.PNInformation b
  on a.PN=b.PN
  where datepart(month,a.ScanDate)=@CurMonth and DATEPART(Week,a.ScanDate)=@CurWeek and DATEPART(WeekDay,a.ScanDate)<=@CurDay and b.Client=@Client group by datepart(WeekDay,a.ScanDate),b.Type;
 
end
update #tb set chrproportion=(case when proportion is null then '' else (cast(cast(proportion as decimal(18,2)) as varchar(100))+'%') end) ; --四舍五入，保留小数点两位

insert into @tbtime(strtime) select distinct strtime from #tb; --获取时间种类

select @strtimeList=@strtimeList+N'['+strtime+N'],',@titlList=@titlList+strtime+',' from @tbtime where strtime like N'%月%';
select @strtimeList=@strtimeList+N'['+strtime+N'],',@titlList=@titlList+strtime+',' from @tbtime where strtime like N'%week%';
select @strtimeList=@strtimeList+N'['+strtime+N'],',@titlList=@titlList+strtime+',' from @tbtime where strtime like N'%号%';

set @titlList=substring(@titlList,0,len(@titlList));
select @titlList;

if(len(@strtimeList)>0)
begin
 set @strtimeList=substring(@strtimeList,0,len(@strtimeList)); --去掉后面的逗号
 set @strsql=N'select strtype as 物料,'+@strtimeList+N' from (select strtime,chrproportion,strtype,
  case strtype when N''PCB'' then 0 when N''FPC'' then 1 when N''IC'' then 2 when N''连接器'' then 3 else 4 end as sort
  from #tb) as SourceTemp1
  PIVOT(max(chrproportion) for strtime in('+@strtimeList+N')) as PIVOTTemp1 order by sort';

 exec sp_executesql @strsql;
end
else
begin
 select * from #tb;
end

drop table #tb;