|
--原贴 http://community.csdn.net/Expert/topic/4200/4200386.xml?temp=.4856989 原始表如下格式：
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 根据Class的值，按日期分别统计出CallCount1，CallCount2，CallCount3。
当该日期无记录时值为0
要求合并成如下格式：
CallDate CallCount1 CallCount2 CallCount3
2005-8-9 0 0 33
2005-8-8 40 77 9
2005-8-7 6 0 21
--创建测试环境
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
--动态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) --结果 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 --删除测试环境 drop table T
|
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 