原始表如下格式：Class;;CallDate;CallCount1 2005-8-8;401 2005-8-7;62 2005-8-8;773 2005-8-9;333 2005-8-8;93 2005-8-7;21
根据Class的值，按日期分别统计出CallCount1，CallCount2，CallCount3。当该日期无记录时值为0要求合并成如下格式：CallDateCallCount1CallCount2CallCount32005-8-90 0 332005-8-8407792005-8-76 0 21
--创建测试环境Create table; T; (Class varchar(2),CallDate datetime, CallCount int)insert into T select '1','2005-8-8',40union all select '1','2005-8-7',6union all select '2','2005-8-8',77union all select '3','2005-8-9',33union all select '3','2005-8-8',9union all select '3','2005-8-7',21--动态SQLdeclare @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 Classset @s=@s+' from T group by CallDate order by CallDate desc 'exec(@s)
--结果
CallDate;;;;CallCount1; CallCount2; CallCount3; ------------------------------------------------------ ----------- ----------- ----------- 2005-08-09 00:00:00.000;;;;;00332005-08-08 00:00:00.000;;;;;40; 77; 92005-08-07 00:00:00.000;;;;;6021
--删除测试环境
drop table T
