How to transform vertical table into horizontal table?
- by avivo
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
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.
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.
