用动态SQL生成动态列

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                                0           0           332005-08-08 00:00:00.000                                40          77          92005-08-07 00:00:00.000                                6           0           21*/--删除测试环境drop table T