CodePudding user response:
The test data-If not object_id (N 'Tempdb for.. # T ') is null
Drop table # T
Go
The Create table # T ([name] nvarchar (22), Date [Date], [revenue] int)
Insert # T
Select N 'zhang', '2019-1-1', 500 union all
Select N 'zhang', '2019-1-2', 400 union all
Select N 'zhang', '2019-1-3', 300 union all
Select N 'zhang', '2019-1-4', 500 union all
Select N 'zhang', '2019-1-5', 500 union all
Select N 'zhang', '2019-1-6', 600
Go
- the end of the test data
DECLARE @ SQL VARCHAR (MAX)
The SET @ SQL='the select [name]'
SELECT SQL + SQL=@ @ ', the sum (case [date] the when "' + RTRIM (date)
+ "' then [revenue] else 0 end) [' + RTRIM (date) + '] '
The FROM (SELECT DISTINCT
Date
The FROM # T WHERE date BETWEEN '2019-1-1' AND '2019-1-3 s'
) a
SQL=SET @ @ SQL
+ ', the sum ([revenue]) subtotals from # T WHERE date BETWEEN ' ' '2019-1-1' AND ', '2019-1-3' 'group by [name]'
The EXEC (@ SQL)
