I have seen this question asked in a few different ways, but none have given the answer I am looking for... Basically I am wanting to turn a Vertical Normalized table into a Horizontal De-Normalized table or view. I am using Oracle 9I on AIX... Consider the following tables...
Table : PERSON
Usage : Contains 1 record per person i.e. John Doe
PERSON_ID - number (unique)
NAME - varchar i.e. John Doe
BIRTHDATE - date i.e. 01/01/1965
Table : PERSON_TRAIT_TYPE
Usage : Contains 1 record per trait we want to track for PERSONS i.e. EyeColor
TRAITTYPE_ID - number (unique)
TRAITTYPE_NAME - varchar i.e. EyeColor, HairColor, Weight etc.
Table : PERSON_TRAIT
Usage : Contains 1 to many record(s) per Person / Trait Type combination we have.. i.e. John Doe - Blue Eyes, Brown Hair
PERSON_TRAIT_ID - number (unique)
TRAITTYPE_ID - fk to person_trait_type.traittype_id
VALUE - varchar i.e. Blue
NEWEST - char (indicates if this is the newest record for this person / trait value.) Y/N
OLDEST - char (indicates if this is the oldest record for this person / trait value.) Y/N
DATE - date (date associated with the insert of this record)
Assume the tables have the following values....
2, Jane Doe
1, Eye Color
2, Hair Color
I would like to see a table or view that produces the following results.
PersonID EyeColor OldDate NewDate HairColor OldDate NewDate Contacts OldDate NewDate
1 Blue 1/10/05 1/10/05 NULL NULL NULL Y 1/15/05 1/15/05
2 Hazel 1/10/05 1/10/05 Blond 1/10/05 2/1/05 NULL NULL NULL
Ok, so the complex part... A person can have many values for the same trait.. For instance Jane Doe changed her hair color from Black to Blond on 02/01/2005, but I still want to know the oldest detail record we have for this trait. Also, this may even be more complex, I dont know how many trait types there may be for a person. There could be an infinate number of traits... For instance we may later decide that we want to track Shoe Size. I dont want to have to change a bunch of code to deal with this.
Thanx in advance for your help!