Group Hierarchy with Aggregation

Important

This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, applications should use XML.

The Data Shaping Service for OLE DB can be used to sum up a hierarchical structure to produce totals at different levels. In this example, certain columns are selected by cust_id from the Customers table, including the order amount; the order information is selected from the Orders table. All data is fetched before it is related and summed. In this example, the resulting child rowset is named rsDetail. RsDetail is summed using the ordered amount for any customer ID within the scope. The resulting Recordset is the parent of rsDetail and contains the summary information of all child rowsets. When accessing a parent row, the internally created index relates the parent to its child rowsets.

SHAPE {select customer.*, orders.order_id, orders.order_amt
         from customers, orders where customer.cust_id = orders.cust_id}
      AS rsDetail
   COMPUTE rsDetail, SUM(rsDetail.order_amt)
      AS order_amt
   COUNT(rsDetail)
      AS DetailCount
   BY cust_id

This results in the following aggregations within this hierarchy: