Sql server® 2012 t-sql fundamentals

The 

GROUPING

 and 

GROUPING_ID

 Functions

When you have a single query that defines multiple grouping sets, you might need to be able to 

associate result rows and grouping sets—that is, to identify for each result row the grouping set 

it is associated with. As long as all grouping elements are defined as NOT NULL, this is easy. For 

example, consider the following query.

SELECT empid, custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY CUBE(empid, custid);

This query produces the following output.

empid       custid    sumqty 

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

2           A         52 

3           A         20 

NULL        A         72 

1           B         20 

2           B         27 

NULL        B         47 

1           C         34 

3           C         22 

NULL        C         56 

3           D         30 

NULL        D         30 

NULL        NULL      205 

1           NULL      54 

2           NULL      79 

3           NULL      72

Because both the empid and custid columns were defined in the dbo.Orders table as NOT NULL, a 

NULL in those columns can only represent a placeholder, indicating that the column did not partici-

pate in the current grouping set. So, for example, all rows in which empid is not NULL and custid is 

not NULL are associated with the grouping set (empid, custid). All rows in which empid is not NULL 

and custid is NULL are associated with the grouping set (empid), and so on. Some people override the 

presentation of NULL marks with ALL or a similar designator, provided that the original columns are 

not nullable. This helps for reporting.

www.it-ebooks.info

---