用动态SQL生成动态列

最新推荐文章于 2024-05-27 14:20:08 发布

jnu2003

最新推荐文章于 2024-05-27 14:20:08 发布

阅读量366

点赞数

分类专栏： SQL 文章标签： sql class table insert 测试

SQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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