把数据从纵向结构转为横向结构

--创建测试数据
create table t(奖惩 varchar(8),次数 int,金额 int,年度 char(4))
insert t
select '大功',2,360,'2002' union all
select '小功',17,1020,'2002' union all
select '小功',3,360,'2002' union all
select '大功',9,1620,'2003' union all
select '嘉獎',2,80,'2003' union all
select '警告',25,-500,'2003' union all
select '警告',1,-40,'2003' union all
select '大功',1,180,'2004' union all
select '嘉獎',12,240,'2004' union all
select '嘉獎',2,80,'2004' union all
select '警告',18,-360,'2004' union all
select '警告',1,-40,'2004' union all
select '大功',3,540,'2005' union all
select '小功',7,420,'2005' union all
select '小過',11,-660,'2005'
go

--执行转换语句
declare @s varchar(8000)
set @s='select isnull(年度,''合计'') as [年度]'
select @s=@s+',['+奖惩+']=sum(case 奖惩 when '''+奖惩+''' then 金额 else 0 end)'+
',['+奖惩+'次数]=sum(case 奖惩 when '''+奖惩+''' then 1 else 0 end)'
from t group by 奖惩
set @s=@s+',合计=sum(金额) from t group by 年度 with rollup'
exec(@s)

结果如下
年度   大功          大功次数        嘉獎          嘉獎次数        警告          警告次数        小功          小功次数        小過          小過次数        合计         
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2002 360         1           0           0           0           0           1380        2           0           0           1740
2003 1620        1           80          1           -540        2           0           0           0           0           1160
2004 180         1           320         2           -400        2           0           0           0           0           100
2005 540         1           0           0           0           0           420         1           -660        1           300
合计   2700        4           400         3           -940        4           1800        3           -660        1           3300

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据