select
系别,
sum( CASE when 校区='北学区' then 实际学时 else 0 end) 北学区,
sum( CASE when 校区='南学区' then 实际学时 else 0 end) 南学区,
sum(实际学时) 总学时
from t
group by 系别
系别,
sum( CASE when 校区='北学区' then 实际学时 else 0 end) 北学区,
sum( CASE when 校区='南学区' then 实际学时 else 0 end) 南学区,
sum(实际学时) 总学时
from t
group by 系别
insert into tb select '材料系' , 150 , '北校区'
union all select '材料系' , 200 , '南校区'
union all select '经管系' , 170 , '北校区'
union all select '建工系' , 180 , '南校区'
union all select '计算机系', 180 , '北校区'
union all select '计算机系', 220 , '南校区'
declare @sql varchar(8000)
set @sql='select 系别'
select @sql=@sql+',['+校区+']=sum(case 校区 when '''+校区+''' then 实际学时 else 0 end)' from tb group by 校区
exec(@sql+',sum(实际学时) as 总学时 from tb group by 系别')drop table tb
declare @t table( dept varchar(12) , real_study int ,sch_dept varchar(12))
insert into @T
select ' 材料系' , 150 , '北校区' union
select ' 材料系' , 200 , '南校区' union
select ' 经管系' , 170 , '北校区' union
select ' 建工系' , 180 , '南校区' union
select ' 计算机系', 180 , '北校区' union
select ' 计算机系', 220 , '南校区'select * from @tselect dept ,
max(case sch_dept when '北校区' then real_study else ' ' end) as 北校区,
max(case sch_dept when '南校区' then real_study else ' ' end) as 南校区,
sum(real_study) as 总计学时
from @t
group by dept