How to transform vertical table into horizontal table?

Question

Hello,

I have one table Person:

Id Name
1  Person1
2  Person2
3  Person3

And I have its child table Profile:

Id PersonId FieldName  Value
1  1        Firstname  Alex
2  1        Lastname   Balmer
3  1        Email      [email protected]
4  1        Phone      +1 2 30004000

And I want to get data from these two tables in one row like this:

Id Name     Firstname Lastname  Email                Phone 
1  Person1  Alex      Balmer    [email protected]  +1 2 30004000

1. What is the most optimized query to get these vertical (key, value) values in one row like this? Now I have a problem that I done four joins of child table to parent table because I need to get these four fields. Some optimization is for sure possible.
2. I would like to be able to modify this query in easy way when I add new field (key,value). What is the best way to do this? To create some StoreProcedure?

I would like to have strongly types in my DB layer (C#) and using LINQ (when programming) so it means when I add some new Key, Value pair in Profile table I would like to do minimal modifications in DB and C# if possible. Actually I am trying to get some best practices in this case.

Answer 1

Select 
    P.ID
    , P.Name
    , Case When C.FieldName = 'FirstName' Then C.Value Else NULL END AS FirstName
    , Case When C.FieldName = 'LastName' Then C.Value Else NULL END AS LastName
    , Case When C.FieldName = 'Email' Then C.Value Else NULL END AS Email
    , Case When C.FieldName = 'Phone' Then C.Value Else NULL END AS Phone  
From Person AS P
Inner JOIN Child AS C
ON P.ID = C.PersonID

You could use PIVOT; not sure which one would be the easiest for you to add a new column.

Answer 2

best optimized way with strongly typed fields, is to do it this way:

CREATE TABLE Persons
(PersonID     int identity(1,1) primary key
,Firstname    varchar(...)
,Lastname     varchar(...)
,Email        varchar(...)
,Phone        varchar(...)
,....
)

then the most optimized query would be:

SELECT
    PersonID,Firstname,Lastname,Email,Phone
    FROM Persons
    WHERE ...

Add all main columns into the persons table. if you need to specialize create additional tables:

--one person can play many instruments with this table
CREATE TABLE PersonMusicians
(PersonID               int           --pk   fk to Persons.PersonID     
,InstrumentCode         char(1)       --pk
,...
)

--only one row per person with this table
CREATE TABLE PersonTeachers
(PersonID               int            --pk   fk to Persons.PersonID    
,FavoriteSubjectCode    char(1)
,SchoolName             varchar(...)
)

if you have to have unlimited dynamic attribute fields, then I would create the above structure as fully as possible (as many common fields as possible) and then have an "AdditionalInfo" table where you store all the info like:

AdditionalInfoFields
FieldID    int identity(1,1) primary key
FieldName  varchar(...)

AdditionalInfo
AdditionalInfoID   int identity(1,1) primary key
PersonID           int   fk to Persons.PersonID  
FieldID            int   fk to AdditionalInfoFields.FieldID    
FieldValue         varchar(..) or you can look into sql_variant

have an index on AdditionalInfo.PersonID+FieldID and if you will search for all people that have attribute X, then also another like AdditionalInfo.FieldID+PersonID

short of any of the above, you will need to use the four left outer joins like you have mentioned in your option #1:

SELECT
    P.ID, p.Name
        , p1.Value AS Firstname
        , p2.value AS Lastname     
        , p3.Value AS Email
        , p4.Value AS Phone
    FROM Persons                 p
        LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Firstname'
        LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Lastname'
        LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Email'
        LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Phone'
    WHERE ....

you could always make a materialized view with an index out of this 4 left join query and have the data precalculated for you which should speed it up.

Answer 3

Here are a few similar questions/answers: One, Two, Three, Four.