| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
On Aug 30, 2:40 pm, tvjoshi <tvjo..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
One possibility:
with backgd_info as(
select employee_id, property_value
from tbl_employee_metadata
where property_cd = 'background_info'),
spec_info as(
select employee_id, property_value
from tbl_employee_metadata
where property_cd = 'special_info')
select e.employee_id, e.employeename, e.email,
b.property_value background_info,
s.property_value special_info
EMPLOYEE_ID EMPLOYEENA EMAIL
BACKGROUND_INFO SPECIAL_INFO
----------- ---------- --------------------
----------------------------------------
----------------------------------------
1 James gmail.com nice
guy special info
2 Karen gmail.com nice
girl no special info
Others may provide additional 'solutions'.
David Fitzjarrell Received on Thu Aug 30 2007 - 15:26:17 CDT
 |
 |