经常用到的交叉表问题,一般用动态SQL能生成动态列！

王朝mssql·作者佚名 2006-01-10

--原贴

原始表如下格式：

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

要求合并成如下格式：

CallDateCallCount1CallCount2CallCount3

2005-8-90 0 33

2005-8-840779

2005-8-76 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