When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, October 06, 1999

Advanced Data Shaping Techniques


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 Professor, Class, and Project tables:

Professor
ProfessorIDAutonumber
FirstNameText
LastNameText
PhoneNumberText

Class
ClassIDAutonumber
ClassNameText
RefNumberText
ProfessorIDNumber

Project
ProjectIDAutonumber
TitleText
ProfessorIDNumber

The ProfessorID in both the Project and 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:

SHAPE {parent SQL statement} [[AS] alias]
APPEND({child SQL statement} [[AS] alias]
RELATE parent column TO child column) [[AS] alias]

To add multiple children, simply do:

SHAPE {parent SQL statement} [[AS] alias]
APPEND({child SQL statement} [[AS] alias]
RELATE parent column TO child column) [[AS] alias]{,
({child SQL statement} [[AS] alias]
RELATE parent column TO child column) [[AS] alias]}

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:

SHAPE {SELECT ProfessorID, FirstName, LastName, PhoneNumber FROM Professor} APPEND({SELECT ClassName, RefNumber, ProfessorID FROM Class} AS Class RELATE ProfessorID TO ProfessorID), ({SELECT Title, ProfessorID FROM Project} AS Project RELATE ProfessorID TO ProfessorID)

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!

  • Read Part 2!

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article