Microsoft® SQL Server 2008 R2 Unleashed

The grouping_id() Function

The grouping_id() function, new in SQL Server 2008, can be used to determine the level of grouping in a query using GROUPING SETS or the CUBE and ROLLUP operators. Unlike the GROUPING() function, which takes only a single column expression as an argument and returns a 1 or 0 to indicate whether that individual column is being aggregated, the grouping_id() function accepts multiple column expressions and returns a bitmap to indicate which columns are being aggregated for that row.

For example, you can add the grouping_id() and grouping() functions to the query in Listing 42.4 and examine the results (see Listing 42.5).

Listing 42.5 Using the grouping_id() Function

Unlike the grouping() function, which takes only a ...