Microsoft® SQL Server 2012 Unleashed

The grouping_id() Function

The grouping_id() function 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 45.47 and examine the results (see Listing 45.48).

LISTING 45.48 Using the grouping_id() Function

Click here to view code image

SELECT c.CustomerID, c.TerritoryID, sod.ProductID, ...