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@gmail.com
       2                      | Karen                             |
karen@gmail.com
       3                      | peter                              |
peter@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@gmail.com | james is
nice guy. | james has special info
       2        | karen               | karen@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