Forgive me, as I am just a grasshopper! (how rude)
anyway, I too was getting:
Incorrect syntax near 'SETS'.
but I am running SQL Server 2008 Management Studio.
Figured out that the Compatibility level must be at 100 in order for this to work. This is a bummer for me, since I work with SQL Server 2005 databases (level 90 or some even set to 80). I think I could really make use of these more powerful grouping techniques, but you are stuck if you aren't using the 2008 engine.
I guess I am forced to continue to do more mental gymnastics to get these types of desired results.:crying: