Microsoft® SQL Server 2008 R2 Unleashed

Sorting Results with the GROUPING Function

When working with the CUBE or ROLLUP operator, SQL Server generates NULL values for the columns that are being rolled up to generate the aggregate values. When you are viewing the results, however, it can be difficult to determine whether the NULL value shown for a nonaggregate column is the result of a rollup or because the column itself contains a NULL value. Fortunately, SQL Server provides the GROUPING function, which you can use to distinguish between real NULL values and NULL values that represent a rollup of all values for a column in the result set.

The GROUPING function returns 1 when the value is grouped and 0 when the column contains a NULL value.

In Listing 43.13, the GROUPING function is ...