|For More Advanced Information on Data Shaping|
|For more advanced information on data shaping be sure to check out the following articles:|
In a previous article, I (Scott Mitchell) showed how to use Data Shaping to return a recordset as an element of another recordset. If you are unfamiliar with Data Shaping, I strongly suggest you read the precursor article first.
Data Shaping, as we discussed earlier, can be used to easily group data which resembles a parent-child relationship. In the previous article, I used the example of professors and classes. Each professor can teach zero to many classes, while each class has exactly one professor. Recall that Data Shaping returns an recordset representing the parent item; in each row, a column represents the children of that particular parent, which is a recordset object itself! So, using Data Shaping, you will have recordsets inside of recordsets. The previous article on Data Shaping discusses this technology in detail.
While single parent-child data shapes may be useful and interesting, the true power of Data Shaping is revealed with multiple children and grandchildren data shapes! Imagine that a particular "parent" has two "children" that you would like to represent as a data shape. For example, in our professor/classes example, imagine that we also have a table that contains a list of all of the research projects that are being worked on. Each research project has exactly one professor assigned to it, and professors can be assigned to multiple research projects. Here are the table definitions for both
ProfessorID in both the
Class tables are foreign keys to the
ProfessorID in the
Professor table. A quick examination of the table designs show that each professor can have zero to many classes and zero to many research projects. Looks like we've got a parent (a professor) with two children (classes taught and research projects). It would be nice to hierarchically display the results: each professor would be listed, followed by the classes he or she taught, and then the research projects he or she worked on.
Such a nice, hierarchical display can be achieved with using Data Shaping. Recall that the syntax for data shaping is:
To add multiple children, simply do:
We can add many children to one data shape. Each child is added by simply adding a comma and another child SQL statement and
RELATE statement. So, to show a our professors, their classes, and their projects, the following data shape command could be used:
Now that we've shown the proper
SHAPE command, let's examine how to write code to utilize it. We'll examine the needed code in Part 2!