--原贴 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
|