How to convert column/row pair to key/value by using SQL?

Two quick options.

More Performant (and offers a bit more flexibility)

 Select A.[PlayerCode]
       ,B.*
  From  YourTable A
  Cross Apply ( values ('Name',[Name])
                      ,('Age' ,concat('',[Age]))
                      ,('Club',[Club])
              ) B([Key],[Value])    

More Dynamic (any number of columns)

 Select A.[PlayerCode]
       ,B.[Key]
       ,B.[Value]
  From  YourTable A
  Cross Apply OpenJSON( (Select A.* For JSON Path,Without_Array_Wrapper ) ) B
  Where [key] not in ('PlayerCode')

Note that we just need to exclude [PlayerCode] and there is no need for conversion of datatypes. Also NULL values will be excluded.

I see there are more answers, but I left my answer here because it is the only one that uses UNPIVOT. Maybe it is useful for you or for any other one:

create table players (
  [PlayerCode] int,
  [Name]   varchar(50),
  [Age] int,
  [Club] varchar(50)
)

insert into players values 
(142, 'Messi', 34, 'PSG'),
(333, 'Ronaldo', 36, 'Manchester United'),
(532, 'Pique', 34, 'FC Barcelona')

SELECT PlayerCode, [Key], Value  
FROM   
   (SELECT PlayerCode, Name, cast(Age as varchar(50)) Age, Club
   FROM players) p  
UNPIVOT  
   (Value FOR [Key] IN   
      (Name, Age, Club)  
)AS unpvt; 

You could use a union:

SELECT PlayerCode, [Key], [Value]
FROM
(
    SELECT PlayerCode, 'Name' AS [Key], Name AS [Value], 1 AS pos FROM yourTable
    UNION ALL
    SELECT PlayerCode, 'Age', CAST(Age AS varchar(3)), 2 FROM yourTable
    UNION ALL
    SELECT PlayerCode, 'Club', Club, 3 FROM yourTable
) t
ORDER BY PlayerCode, pos;