分类统计（急）
B   a1   a2   a3   a4  
1   1     2     3     4
1   1     2     3     4
1   1     2     3     4
1   1     2     3     4
2   1     2     3     4
2   2     2     3     4
2   1     2     3     4
2   1     2     3     4

我想以Ｂ分类统计a1,a2,a3,a4并求a1,a2,a3,a4得到
１　４　８　１２　１６　４０
２　４　８　１２　１６　４０    
        8       16     24           32       80

------解决方案--------------------
Select
B,
SUM(a1) As a1,
SUM(a2) As a2,
SUM(a3) As a3,
SUM(a4) As a4
From TEST
Group By B
With RollUp
------解决方案--------------------
if object_id( 'pubs..tb ') is not null
drop table tb
go

create table tb(B int,a1 int,a2 int,a3 int,a4 int)

insert into tb(B,a1,a2,a3,a4) values(1, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(1, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(1, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(1, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(2, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(2, 2, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(2, 1, 2, 3, 4)
insert into tb(B,a1,a2,a3,a4) values(2, 1, 2, 3, 4)

select isnull(cast(b as varchar), '合计 ') b,sum(a1) a1,sum(a2) a2,sum(a3) a3,sum(a4) a4 ,sum(a1+a2+a3+a4) 合计 from tb group by b with rollup

drop table tb

/*
b a1 a2 a3 a4 合计
----- ----------- ----------- ----------- ----------- -----------
1 4 8 12 16 40
2 5 8 12 16 41
合计 9 16 24 32 81

（所影响的行数为 3 行）
*/