How to: Convert vertical table into horizontal table

From: tvjoshi <tvjoshi_at_gmail.com>
Date: Thu, 30 Aug 2007 12:29:09 -0700
Message-ID: <1188502149.549370.210050@q3g2000prf.googlegroups.com>

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                            |
james_at_gmail.com
        2                      | Karen                             |
karen_at_gmail.com
        3                      | peter                              |

peter_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              | james_at_gmail.com | james is
nice guy. | james has special info
        2        | karen               | karen_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:29:09 CDT