SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE Table #Name(Name_id int, NameValue nvarchar(255));

INSERT #Name(Name_id, NameValue) VALUES
(50,          N'Hannah'),
(51,          N'Jeremy'),
(52,          N'Luna');

CREATE Table #AttValue(Name_id int, AttributeValue_Id int, AttributeValue nvarchar(255));

INSERT #AttValue(Name_id, AttributeValue_id, AttributeValue) VALUES

(50,       11,                 N'01216892584'),
(50,       12,                 N'26'),
(50,       13,                 N'Female');

CREATE Table #Attribute(AttributeValue_id int, AttributeName nvarchar(255));

INSERT #Attribute(AttributeValue_id, AttributeName) VALUES
(11,                 N'Phone Number'),
(12,                 N'Age'),
(13,                 N'Gender');
9 rows affected

-- Conditional aggregation

    SELECT 
      N.NameValue,
      PhoneNumber = MAX(case when A.AttributeName = 'Phone Number' 
                    THEN AV.AttributeValue end),
      Age         = MAX(case when A.AttributeName = 'Age' 
                    THEN AV.AttributeValue end),
      Gender      = MAX(case when A.AttributeName = 'Gender' 
                    THEN AV.AttributeValue end)
    FROM #Name AS N
    INNER JOIN #AttValue AS AV ON N.Name_id = AV.Name_id
    INNER JOIN #Attribute AS A ON AV.AttributeValue_id = A.AttributeValue_id
    GROUP BY N.NameValue;


NameValue
PhoneNumber
Age
Gender


Hannah
01216892584
26
Female


Warning: Null value is eliminated by an aggregate or other SET operation.


-- Pivot

    ;WITH src AS
    (
      SELECT N.NameValue, A.AttributeName, AV.AttributeValue
        FROM #Name AS N
        INNER JOIN #AttValue AS AV ON N.Name_id = AV.Name_id
        INNER JOIN #Attribute AS A ON AV.AttributeValue_id = A.AttributeValue_id
    )
    SELECT 
      p.NameValue, 
      PhoneNumber = p.[Phone Number],
      p.Age, 
      p.Gender
    FROM src PIVOT (MAX(AttributeValue)
      FOR AttributeName IN ([Phone Number], [Age], [Gender])) AS p;


NameValue
PhoneNumber
Age
Gender


Hannah
01216892584
26
Female