Home > Article > Backend Development > For frequently used crosstab problems, dynamic SQL can generally be used to generate dynamic columns! _PHP Tutorial
For frequently used crosstab problems, dynamic SQL can generally be used to generate dynamic columns! _PHP Tutorial
- WBOYOriginal
- 2016-07-13 17:00:35895browse
The original table has the following 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
According to the value of Class, CallCount1, CallCount2, and CallCount3 are counted by date.
When there is no record for this date, the value is 0
Required to be merged 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 test environment
Create table T (Class varchar(2),CallDate datetime, CallCount int)
insert into T select '1',' 2005-8-8',40
union all select '1','2005-8-7',6
union all select '2','2005-8-8',77
union all select '3','2005-8-9',33
union all select '3','2005-8-8',9
union all select '3','2005-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
2005-08-08 00:00:00.000 40 77 9
2005-08- 07 00:00:00.000 6 0 21
--Delete test environment
drop table T