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

Posted on 2005-03-15
Medium Priority
Last Modified: 2010-08-05
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!
Question by:mddatacor
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
LVL 44

Expert Comment

ID: 13550831
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.


Author Comment

ID: 13552281
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!
LVL 22

Expert Comment

by:Helena Marková
ID: 13552571
I think that you can use Oracle 9i Discoverer.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


Expert Comment

ID: 13553480
 ,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

Author Comment

ID: 13559073

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.


Accepted Solution

Jankovsky earned 2000 total points
ID: 13562860
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

LVL 44

Expert Comment

ID: 15760274
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question