示例图!
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([name] nvarchar(22),[date] Date,[value] int)
Insert #T
select N'吉斌','2017-07-01',10 union all
select N'吉斌','2017-07-02',9 union all
select N'吉斌','2017-07-03',4 union all
select N'梁军','2017-07-01',10 union all
select N'梁军','2017-07-02',10 union all
select N'梁军','2017-07-03',17
Go
--测试数据结束
declare @sql varchar(8000)
declare @sql1 varchar(8000)
set @sql='select Name as '+'部门'
set @sql1='select ''总计'''
select @sql=@sql+' , max(case [date] when '''+RTRIM([date])+''' then [value] else 0 end) ['+ RTRIM([date])+DATENAME(dw,[date]) +']'
from (select distinct [date] from #T) as a order by riqi
select @sql1=@sql1+' , sum(['+RTRIM([date])+DATENAME(dw,[date])+'])'
from (select distinct [date] from #T) as a order by riqi
set @sql=@sql+',sum(value) 总计 from #T group by name'
set @sql1=@sql1+',sum(总计) 总计 from ('+@sql+')t'
EXEC(@sql + ' union '+@sql1)
![](http://img.bbs.csdn.net/upload/201707/07/1499407816_787923.jpg)