Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to: Convert vertical table into horizontal table

Re: How to: Convert vertical table into horizontal table

From: tvjoshi <tvjoshi_at_gmail.com>
Date: Thu, 30 Aug 2007 12:40:36 -0700
Message-ID: <1188502836.689455.39650@q5g2000prf.googlegroups.com>


Sorry guys.. table formatting got messed up. This is another attempt to post same question so that formatting is proper.

Hello friends,

I have 2 tables. One store employee information attributes as columns of table in horizontal form.
Other store same employee's more information in a vertical form, in a key-value pair. Let me explain by example.

TBL_EMPLOYEE



Employee_id (int, pk)
EmployeeName (varchar)
Email (varchar)

Employee_id | Name | Email

        1           | James | gmail.com
        2           | Karen | gmail.com
        3           | peter  | gmail.com

TBL_EMPLOYEE_METADATA


Employee_id (int, pk, fk)
Property_Cd (varchar)
Property_value(varchar)

Employee_id | Property_Cd        | Property_value
         1          | backgrnd_info      | nice guy.
         1          | special_info          | special info
         1          | field_info              | in abc field
         2          | background_info  | nice girl
         2          | special_info          | no special ifo
         2          | field_info              | no work in field

The reason for other table is, these attributes may be different and will be decidedly dynamically for each employee.

now while displaying data, I need to show employee data from both tables in a single grid in following way:

EmployeeId | Name | Email         | backgrnd_info | special_info
        1         | James | gmail.com  | nice guy.         | special
info
        2         | karen  | gmail.com  | nice girl.          | no
special ifo

So essentially, i need to transpose information in other table, so that it can be shown horizontally with data in the first table.

There are 2 ways I can think of, to achieve this - 1. Do it on front end code by fetching both tables and then loop through the other table to selectively add columns and data to first table.
2. write a stored procedure, fetch data from 2nd table and dynamically create a horizontal temp table out of it and then join with first table and return data.

Has anyone done anything like this before? Is there any better, easier way to do this, on database side?

Thanks a ton in advance,
tvjoshi

On Aug 30, 12:29 pm, tvjoshi <tvjo..._at_gmail.com> wrote:
> Hello friends,
>
> I have 2 tables. One store employee information attributes as columns
> of table in horizontal form.
> Other store same employee's more information in a vertical form, in a
> key-value pair. Let me explain by example.
>
> TBL_EMPLOYEE
> =========
> Employee_id (int, pk) | EmployeeName (varchar) | Email (varchar)
> 1 | James |
> ja..._at_gmail.com
> 2 | Karen |
> ka..._at_gmail.com
> 3 | peter |
> pe..._at_gmail.com
>
> TBL_EMPLOYEE_METADATA
> =================
> Employee_id (int, pk, fk) | Property_Cd (varchar) |
> Property_value(varchar)
> 1 | background_info | james
> is nice guy.
> 1 | special_info |
> James has special info
> 1 | field_info |
> James works in abc field
> 2 | background_info | Karen
> is nice girl
> 2 | special_info |
> Karen has no special ifo
> 2 | field_info |
> she does not work in field
>
> The reason for other table is, these attributes may be different and
> will be decidedly dynamically for each employee.
>
> now while displaying data, I need to show employee data from both
> tables in a single grid in following way:
>
> EmployeeId | EmployeeName | Email |
> background_info | special_info
> 1 | James | ja..._at_gmail.com | james is
> nice guy. | james has special info
> 2 | karen | ka..._at_gmail.com | karen is
> nice girl. | karen has no special ifo
>
> So essentially, i need to transpose information in other table, so
> that it can be shown horizontally with data in the first table.
>
> There are 2 ways I can think of, to achieve this -
> 1. Do it on front end code by fetching both tables and then loop
> through the other table to selectively add columns and data to first
> table.
> 2. write a stored procedure, fetch data from 2nd table and dynamically
> create a horizontal temp table out of it and then join with first
> table and return data.
>
> Has anyone done anything like this before? Is there any better, easier
> way to do this, on database side?
>
> Thanks a ton in advance,
> tvjoshi
Received on Thu Aug 30 2007 - 14:40:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US