haagendaz (11/26/2012)

---

I have 2 related tables and the second table can have many rows associated with the parent table. I need to join all of rows in table 2 to the parent row in table 1 by adding columns to the row based on the child row "name". I am probably doing a terrible job at explaining this so please see example below.

Instead of creating a table (Person) with all the the metadata (PersonAttributes) in 1 table I broke it into 2 because the attributes may vary. I would like to try to accomplish the below without having to modify any special permissions....for example to use OPENROWSET.

Person Table layout:

Id - Identity

FirstName

LastName

Address

ect.

PersonAttributes Table layout:

Id - Identity

PersonId - link to "Person"

Name - HairColor, EyeColor, Weight, ect.

Value - Red, blue, 175, ect.

I would like my end result to look like this (the end result could be a little different for each person because some may have more or less attributes than others.

Dynamic table row output 1:

Fields: Id, FirstName, LastName, Address, HairColor, Weight

Values: 1, Mickey, Mouse, 123 Majic kingdom Lane, Black, 375

Dynamic table row output 2:

Fields: Id, FirstName, LastName, Address, HairColor, EyeColor

Values: 2, Minnie, Mouse, ABC Palace Lane, Pink, Green

The above is an example of my real data layout...I hope it is sufficient in explaining my problem.

Thank you in advance!

Dustin