动态SQL，交叉表例子

感觉就是把竖着的N条记录，查成横向的很多列，搬个例子记录下。

思路：通过查询原表的记录，动态去构造一个varchar变量，然后调用exec()，执行该变量中的sql语句。

原始表格式如下：

Class	CallDate	CallCount
1	2005-8-8	40
1	2005-8-7	6
2	2005-8-8	77
2	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)

--删除测试环境
drop table T

 

--结果

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