>>I think this solution is as pretty as it gets and you don't find anything better.
>>
>
>Whether PIVOT or CASE is prettier/cleaner - that's subjective and everyone can decide for themselves. (Some lengthy debates on the MSDN forums for years and I know you were part of the discussions)
>
>My personal opinion is that I'd rather read a PIVOT statement than a CASE statement, especially for a long query. The long case statements can get a bit cluttered, but I realize many experienced SQL people don't like PIVOT. Just my 2 cents.
>
>It would be nice if there was a more elegant solution but I doubt you'll ever see that covered in an ANSI SQL standard. (Yes, someone could use dynamic SQL for a long list like this one).
>
>But on the "better" - while SQL will usually generate identical execution plans, I would argue that the PIVOT is, more often, a bit more efficient on the total elapsed time from IO statistics (certainly not by a significant amount)

In this particular case we need SUM and COUNT and also total SUM and total COUNT. It means if we need to use PIVOT syntax, we would need to do it somehow twice. That's why from the efficiency point of the view this is already an optimal solution.

When we have a simpler situation where PIVOT syntax can be applied, then there is no difference in the query plans and it's up for the person designing the query which syntax he/she would prefer.