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

WBOY
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


www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631231.htmlTechArticleThe 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, CallC is calculated by date...
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn