Emulate ROLLUP, CUBE, and GROUPING SETS where they're not supported natively

[lukaseder]

Here's how:

-- ROLLUP() with one argument 
SELECT AUTHOR_ID, COUNT(*)
FROM T_BOOK
GROUP BY ROLLUP(AUTHOR_ID)

-- ROLLUP() with two arguments
SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*)
FROM T_BOOK
GROUP BY ROLLUP(AUTHOR_ID, PUBLISHED_IN)

With UNION ALL:

-- The same query using UNION ALL:
  SELECT AUTHOR_ID, COUNT(*) FROM T_BOOK GROUP BY (AUTHOR_ID)
UNION ALL
  SELECT NULL, COUNT(*) FROM T_BOOK GROUP BY ()
ORDER BY 1 NULLS LAST

-- The same query using UNION ALL:
  SELECT AUTHOR_ID, PUBLISHED_IN, COUNT(*) 
  FROM T_BOOK GROUP BY (AUTHOR_ID, PUBLISHED_IN)
UNION ALL
  SELECT AUTHOR_ID, NULL, COUNT(*) 
  FROM T_BOOK GROUP BY (AUTHOR_ID)
UNION ALL
  SELECT NULL, NULL, COUNT(*) 
  FROM T_BOOK GROUP BY ()
ORDER BY 1 NULLS LAST, 2 NULLS LAST

This is also nicely explained here:
http://msdn.microsoft.com/en-us/library/bb510427(v=sql.105)

Prerequisites for this task:

• Introduce new type GroupField for cube(), rollup() and groupingSets() functions. Accept only Field... and GroupField... in groupBy() clauses #1038 Formal GroupField type
• Add support for the empty GROUP BY () clause #1665 GROUP BY() support