业务机构     商品类别        销售金额
1001            A               20000
1001            B               60000
1002            A               30000
1002            B               90000


我想要得到一个汇总的表，如下情况：

业务机构     商品类别        销售金额
1001            A               20000
1001            B               60000
合计                            80000
1002            A               30000
1002            B               90000
合计                            120000
总合计                          200000

如果数据量不大的话手动处理一下。

select * from t_TableA

select CASE  WHEN GROUPING(Dept)=1 THEN '合计' WHEN GROUPING(Goodscode)=1 THEN  '(小计)' ELSE CONVERT(varchar(355),Dept) END,
        Goodscode,
        SUM(Amount) AS Amount

 from t_TableA

 WHERE 1=1

 GROUP BY Dept,goodscode WITH ROLLUP

小型的可以自己写，工作量不大