Group-Based Hierarchies

The Data Shaping Service for OLE DB can be used to create a group-based hierarchy from a single rowset.

In group-based relationships, the BY clause in the SHAPE command is used to partition a rowset. A parent rowset is then constructed, in which there is one row for each partition in the child rowset.

If the BY clause is omitted, the child rowset is divided into a single partition and the parent rowset contains only a single row.

The parent rowset must contain a chapter column that relates it to its defining child rowset. The COMPUTE clause adds this column to the parent rowset. The parent rowset can also contain other columns, similar to a relation-based rowset. This includes chapter columns, so a computed parent rowset might have multiple child rowsets.

In the following example, the child rowset, rsOrders, consists of the results of the SELECT statement. The COMPUTE clause defines the parent rowset to be built from rsOrders. In the COMPUTE clause, rsOrders AS chpOrders identifies rsOrders as the defining child rowset. It also adds a chapter column, chpOrders, to the parent rowset. The BY cust_id clause identifies how the child rowset should be partitioned into chapters. There will be a single chapter (and a single parent row) for each distinct value of cust_id in rsOrders. The BY clause also adds a cust_id column to the parent rowset.

This grouped hierarchical data can now become a component of an even larger hierarchy; multiple groupings are allowed.

SHAPE {select orders.order_id, orders.cust_id, orders.order_amt
      from Orders }AS rsOrders
   COMPUTE rsOrders
      AS chpOrders
BY cust_id

This results in the following hierarchy:

Chapters