Sql server® 2012 t-sql fundamentals

 

CHAPTER 7

 

Beyond the Fundamentals of Querying

 

 233

Suppose that instead of four separate result sets, you wanted a single unified result set with the ag-

gregated data for all four grouping sets. You could achieve this by using the UNION ALL set operation 

to unify the result sets of all four queries. Because set operations require all result sets to have compat-

ible schemas with the same number of columns, you need to adjust the queries by adding placeholders 

(for example, NULL marks) to account for missing columns. Here’s what the code would look like.

SELECT empid, custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY empid, custid 

 

UNION ALL 

 

SELECT empid, NULL, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY empid 

 

UNION ALL 

 

SELECT NULL, custid, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY custid 

 

UNION ALL 

 

SELECT NULL, NULL, SUM(qty) AS sumqty 

FROM dbo.Orders;

This code generates a single result set, with the aggregates for all four grouping sets being unified. 

empid       custid    sumqty 

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

2           A         52 

3           A         20 

1           B         20 

2           B         27 

1           C         34 

3           C         22 

3           D         30 

1           NULL      54 

2           NULL      79 

3           NULL      72 

NULL        A         72 

NULL        B         47 

NULL        C         56 

NULL        D         30 

NULL        NULL      205 

 

(15 row(s) affected)

Even though you managed to get what you were after, this solution has two main problems—the 

length of the code and the performance. This solution requires you to specify a whole GROUP BY 

query for each grouping set. When you have a large number of grouping sets, the query can get 

quite long. Also, to process the query, SQL Server will scan the source table separately for each query, 

which is inefficient.

www.it-ebooks.info

---

234  

Microsoft SQL Server 2012 T-SQL Fundamentals

SQL Server supports several features that follow standard SQL and address the need to define mul-

tiple grouping sets in the same query. Those are the GROUPING SETS, CUBE, and ROLLUP subclauses 

of the GROUP BY clause and the GROUPING and GROUPING_ID functions.