Cross tabulation is frequently used. Dynamic columns can be generated using dynamic SQL !. Original table format: ClassCallDateCallCount12005-8-84012005-8-7622005-8-87732005-8-93332005-8-8932005-8-721 according to the value of Class, respectively according to the date CallC original table format:
Class CallDate CallCount
1 2005-8-8 40
1 2005-8-7 6
2 2005-8-8 77
3 2005-8-9 33
3 2005-8-8 9
3 2005-8-7 21
CallCount1, CallCount2, and CallCount3 are calculated by date based on the Class values.
When no record value for this date is set to 0
Merge the data into the following format:
CallDate CallCount1 CallCount2 CallCount3
2005-8-9 0 0 33
2005-8-8 40 77 9
2005-8-7 6 0 21
-- Create a test environment
Create table T (Class varchar (2), CallDate datetime, CallCount int)
Insert into T select '1', '2017-8-8 ', 40
Union all select '1', '2014-8-7 ', 6
Union all select '2', '2017-8-8 ', 77
Union all select '3', '2014-8-9 ', 33
Union all select '3', '2017-8-8', 9
Union all select '3', '2014-8-7 ', 21
-- Dynamic SQL
Declare @ s varchar (8000)
Set @ s = 'SELECT calldate'
Select @ s = @ S', [CallCount 'class'] = sum (case when Class = '''class''' then CallCount else 0 end )'
From T
Group by Class
Set @ s = @ s' from T group by CallDate order by CallDate desc'
Exec (@ s)
-- Result
CallDate CallCount1 CallCount2 CallCount3
---------------------------------------------------------------------------------------
2005-08-09 00:00:00. 000 0 0 33
00:00:00. 000 40 77 9
2005-08-07 00:00:00. 000 6 0 21
-- Delete the Test environment
Drop table T
Keep Class CallDate CallCount 1 2005-8-8 40 1 2005-8-7 6 2 2005-8-8 77 3 2005-8-9 33 3 2005-8-8 9 3 2005-8-7 21 according to the Class value, calculate CallC by date...
