Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to: Convert vertical table into horizontal table
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 | 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. | nospecial 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