Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 237

However, if a grouping column is defined as allowing NULL marks in the table, you cannot tell for 

sure whether a NULL in the result set originated from the data or is a placeholder for a nonpartici-

pating member in a grouping set. One way to determine grouping set association in a deterministic 

manner, even when grouping columns allow NULL marks, is to use the GROUPING function. This 

function accepts a name of a column and returns 0 if it is a member of the current grouping set and 1 

otherwise. 

note

  I find it counterintuitive that the GROUPING function returns 1 when the element 

isn’t part of the grouping set and 0 when it is. To me, it would have made more sense for 

the function to return 1 (meaning true) when the element is part of the grouping set and 

0 otherwise. But that’s the implementation, so you just need to make sure that you realize 

this fact.

For example, the following query invokes the GROUPING function for each of the grouping 

elements.

SELECT 

  GROUPING(empid) AS grpemp, 

  GROUPING(custid) AS grpcust, 

  empid, custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY CUBE(empid, custid);

This query returns the following output.

grpemp    grpcust    empid       custid    sumqty 

--------- ---------- ----------- --------- ----------- 

0         0          2           A         52 

0         0          3           A         20 

1         0          NULL        A         72 

0         0          1           B         20 

0         0          2           B         27 

1         0          NULL        B         47 

0         0          1           C         34 

0         0          3           C         22 

1         0          NULL        C         56 

0         0          3           D         30 

1         0          NULL        D         30 

1         1          NULL        NULL      205 

0         1          1           NULL      54 

0         1          2           NULL      79 

0         1          3           NULL      72 

 

(15 row(s) affected)

www.it-ebooks.info

---

238  

Microsoft SQL Server 2012 T-SQL Fundamentals

Now you don’t need to rely on the NULL marks anymore to figure out the association between 

result rows and grouping sets. For example, all rows in which grpemp is 0 and grpcust is 0 are associ-

ated with the grouping set (empid, custid). All rows in which grpemp is 0 and grpcust is 1 are associated 

with the grouping set (empid), and so on.

SQL Server supports another function called GROUPING_ID that can further simplify the process of 

associating result rows and grouping sets. You provide the function with all elements that participate 

in any grouping set as inputs—for example, GROUPING_ID(a, b, c, d)—and the function returns an 

integer bitmap in which each bit represents a different input element—the rightmost element repre-

sented by the rightmost bit. For example, the grouping set (a, b, c, d) is represented by the integer 0 

(0×8 + 0×4 + 0×2 + 0×1). The grouping set (a, c) is represented by the integer 5 (0×8 + 1×4 + 0×2 + 

1×1), and so on.

Instead of calling the GROUPING function for each grouping element as in the previous query, 

you can call the GROUPING_ID function once and provide it with all grouping elements as input, as 

shown here.

SELECT 

  GROUPING_ID(empid, custid) AS groupingset, 

  empid, custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY CUBE(empid, custid);

This query produces the following output.

groupingset    empid       custid    sumqty 

-------------- ----------- --------- ----------- 

0              2           A         52 

0              3           A         20 

2              NULL        A         72 

0              1           B         20 

0              2           B         27 

2              NULL        B         47 

0              1           C         34 

0              3           C         22 

2              NULL        C         56 

0              3           D         30 

2              NULL        D         30 

3              NULL        NULL      205 

1              1           NULL      54 

1              2           NULL      79 

1              3           NULL      72

Now you can easily figure out which grouping set each row is associated with. The integer 0 

(binary 00) represents the grouping set (empid, custid); the integer 1 (binary 01) represents (empid); 

the integer 2 (binary 10) represents (custid); and the integer 3 (binary (11) represents (). 

www.it-ebooks.info

---