OK, Metin made a good point - not everyone can upgrade to SQL 2012.

So I'll go back in time, back to SQL 2008. Suppose you wanted to generate this kind of result set (see attached), where you have a grand total row.

Historically, developers would use a UNION, or create a temp table and then insert a grand total row manually (or maybe even other approaches). Some might even use GROUP BY ROLLUP or GROUP BY CUBE.

All of them certainly work, but T-SQL 2008 implemented a new feature called GROUPING SETS. It's the "holy grail" of grouping. Just like the name implies, your result set can have multiple GROUP BY definitions, as you can see from the code below. You can use GROUPING SETS for several different scenarios - one common one is to simply generate a total row.

The code below also demonstrates a basic example of PIVOT - and generates two CTEs (Common Table Expressions). First, a subquery just to get the orders expressed in terms of quarter, and then a 2nd CTE to pivot the Columns as quarters, and then a final query that uses GROUPING SETSs.

It's very late here, I'll post a reply and talk about GROUPING SETS and this query a little more tomorrow.

use AdventureWorks2008
GO
;with TempCTE as
   (select SM.Name as ShipperName, datepart(q,OrderDate) as OrderQtr, sum(POH.TotalDue) as TotDue
      from Purchasing.PurchaseOrderHeader POH
	      JOIN Purchasing.ShipMethod SM   on POH.ShipMethodID  = sm.ShipMethodID
		   where year(OrderDate) = 2007
		   group by SM.Name, datepart(q,OrderDate)
		   ) , 
PivotedCTE as 
(select  ShipperName, [1] as Q1, [2] as Q2, [3] as Q3, [4] as Q4
    from TempCTE
	   pivot ( sum(TotDue) for OrderQtr in ( [1], [2], [3], [4])) TempAlias )

select isnull(ShipperName, '*TOTAL*') AS ShipperName, SUM (Q1) AS Q1, SUM(Q2) AS Q2, SUM(Q3) AS Q3, SUM(Q4) AS Q4
   FROM PivotedCTE
   group by grouping sets
               (    (ShipperName),   ()  )   -- note the two sets of GROUPs....by shipper name, and then a grand total with empty parenthesis