Grouping Hierarchy

A grouping hierarchy involves only one recordset. The concept is similar to a GROUP BY clause in an SQL statement: Rows with a common column value are grouped together, and aggregate columns can be generated (for example, using SUM or AVG). However, the ADO hierarchy feature makes this more interesting than standard grouping. The aggregate values are stored in a parent recordset, and all of the children for that group are stored in the child recordset. This can be very useful for generating reports.

Although only one source recordset is used in the group hierarchy, two recordsets are returned from executing the command. The parent recordset contains group and aggregate information, and the child recordset contains the actual details of the individual rows. Consider the following example, which creates a two-recordset hierarchy.

Note In the following example, CustCount is the calculated count of the number of customers in each group. RS1 is a recordset-valued column. (The type for the Field.Type property is adChapter, to reflect the OLE DB column type DBTYPE_CHAPTER.)

SHAPE  {select customerid, region from customers} rs1 
   COMPUTE COUNT(rs1.customerid) As CustCount, rs1 By region

The shape of the resulting hierarchy is as follows:

CustCount
Rs1
   |
   +----customerid
      region

Note The ADO documentation expands on the examples given here as well as describing the full grammar.