In real life, you often have to work with data in tables that aren't properly normalized. For example, let's say you want to list in alphabetical order all the residents of an apartment building. This task would be easy with a normalized database, which would have one table to hold just the names of individual residents, each of whom might or might not share an apartment unit with someone else. But in our real-life scenario, the database developer (unwisely) created a table for each apartment, using an array, or repeating group, to store each resident's name.
Listing 1 shows the denormalized Id_Residents table and all its columns. Ugh! The developer thought that because no more than five residents would live in any one apartment (location_id) at a time, creating a repeating group was OK. But suppose someone requests an alphabetized view of all residents in the entire building (as happened to me). How do you create this view without using temporary tables, cursors, or other resource-hungry tools?
My solution was to use the UNION statement along with a subquery that produces a derived table, as the code in Listing 2 shows. First, the subquery creates a derived table that consists of all the names in the Id_Residents table joined by the UNION statement. (Note the alias after the subquery; the alias is necessary when you're using derived tables.) The outer query then selects from this derived table and orders the data alphabetically. Note that the TOP n statement in the view definition is necessary in a view whenever you're using ORDER BY.
—Cade Bryant
cadebryant@hotmail.com