How to transform vertical table into horizontal table?

You can use the PIVOT operator for this.

Here you have an example for your task:

DECLARE @person TABLE (
    Id int,
    Name varchar(10)
)

INSERT INTO @person (id, Name)
SELECT 1, 'Person1' UNION ALL
SELECT 2, 'Person2' UNION ALL
SELECT 3, 'Person3' UNION ALL
SELECT 4, 'Person4'

DECLARE @profile TABLE (
    Id int,
    PersonId int,
    FieldName varchar(50),
    Value varchar(50)
)

INSERT INTO @profile (Id, PersonID, FieldName, Value)
SELECT 1, 1, 'Firstname', 'Alex' UNION ALL
SELECT 2, 1, 'Lastname', 'Balmer' UNION ALL
SELECT 3, 1, 'Email', 'some_email@test.com' UNION ALL
SELECT 4, 1, 'Phone', '+1 2 30004000'

;WITH PivotTable AS (
    SELECT 
        PersonId,
        Firstname,
        Lastname,
        Email,
        Phone
    FROM
    (
        Select 
            PersonId,
            FieldName,
            Value
        From @profile
    ) p
    PIVOT
    (
        MIN(Value)
        FOR FieldName IN ([Firstname], [Lastname], [Email], [Phone])
    ) AS pvt
)
SELECT
    P.Id,
    P.Name,
    PT.Firstname,
    PT.Lastname,
    PT.Email,
    PT.Phone
FROM PivotTable PT
INNER JOIN @person P ON PT.PersonID = P.ID

For more inforamtion look on MSDN

First, it would be a big help to everyone if you posted data in a readily consumable format. That includes table creation statments. Pavel took the time to do that and I'll do it as well this time just to show another method. It almost guarantees that you'll get a coded/tested answer and it identifies the data types we need to work with. Here's the test setup code...

--===== Work in a nice safe place that everyone has...
    USE TempDB
;
--===== Create and populate the Person table
 CREATE TABLE dbo.Person 
        (Id INT, Name VARCHAR(30))
;
 INSERT INTO dbo.Person  
        (Id,Name)
 SELECT 1,'Person1' UNION ALL
 SELECT 2,'Person2' UNION ALL
 SELECT 3,'Person3'
;
--===== Create and populate the Profile table
 CREATE TABLE dbo.Profile
        (Id INT, PersonId INT, FieldName VARCHAR(50), Value VARCHAR(100))
 INSERT INTO dbo.Profile
        (Id,PersonId,FieldName,Value)
 SELECT 1,1,'Firstname','Alex' UNION ALL
 SELECT 2,1,'Lastname','Balmer' UNION ALL
 SELECT 3,1,'Email','some_email@test.com' UNION ALL
 SELECT 4,1,'Phone','+1 2 30004000'
;

Here's another way to do the same thing Pavel did for an answer... it's called a "CROSS TAB" and is usually just a bit faster than a pivot...

--===== Now, solve the problem using a nice little CROSS TAB
 SELECT person.ID,
        person.Name,
        MAX(CASE WHEN profile.FieldName = 'FirstName' THEN Value END) AS FirstName,
        MAX(CASE WHEN profile.FieldName = 'Lastname'  THEN Value END) AS LastName,
        MAX(CASE WHEN profile.FieldName = 'Email'     THEN Value END) AS Email,
        MAX(CASE WHEN profile.FieldName = 'Phone'     THEN Value END) AS Phone
   FROM dbo.Person person
  INNER JOIN dbo.Profile profile
     ON person.ID = profile.PersonID
  GROUP BY person.ID, person.Name

Please see the following article for why... http://www.sqlservercentral.com/articles/T-SQL/63681/