--测试数据 if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([姓名] nvarchar(22),[日期] Date,[收入] int) Insert #T select N'张三','2019-1-1',500 union all select N'张三','2019-1-2',400 union all select N'张三','2019-1-3',300 union all select N'张三','2019-1-4',500 union all select N'张三','2019-1-5',500 union all select N'张三','2019-1-6',600 Go --测试数据结束 DECLARE @sql VARCHAR(MAX) SET @sql = 'select [姓名]' SELECT @sql = @sql + ',sum(case [日期] when ''' + RTRIM(日期) + ''' then [收入] else 0 end)[' + RTRIM(日期) + ']' FROM ( SELECT DISTINCT 日期 FROM #T WHERE 日期 BETWEEN '2019-1-1' AND '2019-1-3' ) a SET @sql = @sql + ',sum([收入]) 小计 from #T WHERE 日期 BETWEEN ''2019-1-1'' AND ''2019-1-3'' group by [姓名]' EXEC(@sql)
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([姓名] nvarchar(22),[日期] Date,[收入] int)
Insert #T
select N'张三','2019-1-1',500 union all
select N'张三','2019-1-2',400 union all
select N'张三','2019-1-3',300 union all
select N'张三','2019-1-4',500 union all
select N'张三','2019-1-5',500 union all
select N'张三','2019-1-6',600
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select [姓名]'
SELECT @sql = @sql + ',sum(case [日期] when ''' + RTRIM(日期)
+ ''' then [收入] else 0 end)[' + RTRIM(日期) + ']'
FROM ( SELECT DISTINCT
日期
FROM #T WHERE 日期 BETWEEN '2019-1-1' AND '2019-1-3'
) a
SET @sql = @sql
+ ',sum([收入]) 小计 from #T WHERE 日期 BETWEEN ''2019-1-1'' AND ''2019-1-3'' group by [姓名]'
EXEC(@sql)