Data Shaping Example

The following data shaping command demonstrates how to build a hierarchical Recordset from the Customers and Orders tables in the Northwind database, which is installed with Microsoft SQL Server 2000.

SHAPE {SELECT CustomerID, ContactName FROM Customers} 
APPEND ({SELECT OrderID, OrderDate, CustomerID FROM Orders} AS chapOrders 
RELATE customerID TO customerID) 

When this command is used to open a Recordset object (as shown in Visual Basic Example of Data Shaping), it creates a chapter (chapOrders) for each record returned from the Customers table. This chapter consists of a subset of the Recordset returned from the Orders table. The chapOrders chapter contains all the requested information about the orders placed by the given customer. In this example, the chapter consists of three columns: OrderID, OrderDate, and CustomerID.

The first two entries of the resultant shaped Recordset are as follows:

In a SHAPE command, APPEND is used to create a child Recordset related to the parent Recordset (as returned from the provider-specific command immediately after the SHAPE keyword above) by the RELATE clause. The parent and child typically have at least one column in common: The value of the column in a row of the parent is the same as the value of the column in all rows of the child.

There is a second way to use SHAPE commands: namely, to generate a parent Recordset from a child Recordset. The records in the child Recordset are grouped, typically using the BY clause, and one row is added to the parent Recordset for each resulting group in the child. If the BY clause is omitted, the child Recordset will form a single group and the parent Recordset will contain exactly one row. This is useful for computing "grand total" aggregates over the entire child Recordset.

The SHAPE command construct also enables you to programmatically create a shaped Recordset. You can then access the components of the Recordset programmatically or through an appropriate visual control. A shape command is issued like any other ADO command text. For more information, see Shape Commands in General.

Regardless of which way the parent Recordset is formed, it will contain a chapter column that is used to relate it to a child Recordset. If you wish, the parent Recordset may also have columns that contain aggregates (SUM, MIN, MAX, and so on) over the child rows. Both the parent and the child Recordset may have columns which contain an expression on the row in the Recordset, as well as columns which are new and initially empty.

This section continues with the following topic.

• Visual Basic Example of Data Shaping