Sql server® 2012 t-sql fundamentals

246  

Microsoft SQL Server 2012 T-SQL Fundamentals

5

If you understand the concept of grouping sets, this exercise should be straightforward for you. You 

can use the GROUPING SETS subclause to list the requested grouping sets and the GROUPING_ID 

function to produce a unique identifier for the grouping set with which each row is associated. Here’s 

the complete solution query.

SELECT 

  GROUPING_ID(empid, custid, YEAR(Orderdate)) AS groupingset, 

  empid, custid, YEAR(Orderdate) AS orderyear, SUM(qty) AS sumqty 

FROM dbo.Orders 

GROUP BY 

  GROUPING SETS 

  ( 

    (empid, custid, YEAR(orderdate)), 

    (empid, YEAR(orderdate)), 

    (custid, YEAR(orderdate)) 

  );

The requested grouping sets are neither a power set nor a rollup of some set of attributes. There-

fore, you cannot use either the CUBE or the ROLLUP subclause to further abbreviate the code. 

www.it-ebooks.info

---