Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

Convert a table that has vertical data to a view with horizontal columns - 500pts

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
Columns :
   PERSON_ID - number (unique)
   NAME - varchar i.e. John Doe
   BIRTHDATE - date i.e. 01/01/1965

Usage : Contains 1 record per trait we want to track for PERSONS i.e. EyeColor
Columns :
   TRAITTYPE_ID - number (unique)
   TRAITTYPE_NAME - varchar i.e. EyeColor, HairColor, Weight etc.

Usage : Contains 1 to many record(s) per Person / Trait Type combination we have.. i.e. John Doe - Blue Eyes, Brown Hair
Columns :
   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....

1,John Doe
2, Jane Doe

1, Eye Color
2, Hair Color
3, Contacts


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!
  • 2
  • 2
  • 2
  • +1
1 Solution
believe me, you DO NOT want to have a table structure as you describe:

"I would like to see a table or view that produces the following results.

PersonID   EyeColor  OldDate    NewDate    HairColor   OldDate    NewDate    Contacts  OldDate NewDate"

How the tables are NOW strucutred is correct, and in line with proper database design.  The new strucutre is NOT in conformance with the rules for database design (there are basically three such rules, called Normalization Forms), and the structure you describe violates the Third Normal Form.

mddatacorAuthor Commented:
No, I get the fact that in a relational database you want to conform to the 3 normalization forms, but what I am trying to do is make this structure query'able in a OLAP tool. The type of structure that we have today does not lend itself well when you are trying to search these tables, or make cubes from these tables in an OLAP world. So, think of this as an attempt to put this table in a Data Warehouse or Data Mart, and the application that will use it will be an OLAP tool, that requires these tables to be flat.

Thanx for your comments!
Helena Markováprogrammer-analystCommented:
I think that you can use Oracle 9i Discoverer.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 ,min("Eye Color")
 ,min("Eye Color Old date")
 ,min("Eye Color New Date")
 ,min("Hair Color")
 ,min("Hair Color Old date")
 ,min("Hair Color New Date")
 ,min("Contacts Old date")
 ,min("Contacts New Date")
 ,case when ptt.traittype_id=1 then ptn.VALUE else null end as "Eye Color"
 ,case when ptt.traittype_id=1 then pto.DATE else null end as "Eye Color Old date"
 ,case when ptt.traittype_id=1 then ptn.DATE else null end as "Eye Color New Date"
 ,case when ptt.traittype_id=2 then ptn.VALUE else null end as as "Hair Color"
 ,case when ptt.traittype_id=2 then pto.DATE else null end as "Hair Color Old date"
 ,case when ptt.traittype_id=2 then ptn.DATE else null end as "Hair Color New Date"
 ,case when ptt.traittype_id=3 then ptn.VALUE else null end as "Contacts"
 ,case when ptt.traittype_id=3 then pto.DATE else null end as "Contacts Old date"
 ,case when ptt.traittype_id=3 then ptn.DATE else null end as "Contacts New Date"
from PERSON p
cross join PERSON_TRAIT_TYPE ptt
left outer join PERSON_TRAIT ptn on p.PERSON_ID=ptn.PERSON_ID and ptn.NEWEST='Y' and ptt.TRAITTYPE_ID=ptn.TRAITTYPE_ID
left outer join PERSON_TRAIT pto on p.PERSON_ID=pto.PERSON_ID and pto.OLDEST='Y' and ptt.TRAITTYPE_ID=ptn.TRAITTYPE_ID
) group by person_id
mddatacorAuthor Commented:

Interesting code, I havent had a change yet to try it out, but the problem that I see with it is that I am going to have to change the code pretty ofted, because these traits are being created on a daily basis. Basically I need some kind of dynamic solution that will change when traittypes are added.

Hi scott,
it's a static approach I wrote.

If you have to do it dynamically, there are two ways:
Either to use seme reporting tool allowing matrix reports (such as Discoverer, Oracle Reports, ...) or to  prepare my query dynamically (I'd choice this way).

You can use it as a stored procedure, creating a view dynamically (e.g. on daily basis) or solve it out of the database within an application

No comment has been added to this question in more than 21 days, so it is now classified as abandoned..
I will leave the following recommendation for this question in the Cleanup topic area:
Accept - Janovsky - #13562860

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now