Super article Jeff -- as always. Thanks especially for writing something that works in SQL 2000 (until you hit the 4000 char limit). A number of us out there are still supporting some SQL 2000 systems.
Here is one for your "Super Ninja" -- use the GROUPING function to help with those gnarly ROLLUPS and CUBES.
SELECT CASE GROUPING(Field1) WHEN 1 THEN 'Total' ELSE Field1 END -- puts "Total" in when rolled up
Order by part:
ORDER BY GROUPING(Field1) ASC, Field1 (sorts the totals to the bottom, change ASC to DESC to sort them to the top)
And finally when using WITH CUBE because you want some of the other dimensions but not all of them, use the HAVING clause to eliminate some of them
HAVING GROUPING(Field3) = 0 -- will not roll up this field into a total
AND GROUPING(Field4) = GROUPING(Field5) -- rolls these up together, which is perfect when Field4 is the ID and Field5 is the description and you don't want to roll them up separately
This works with WITH ROLLUP too!