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

Question

[PlayerCode] | [Name]   | [Age] | [Club]
142          | Messi    | 34    | PSG
333          | Ronaldo  | 36    | Manchester United
532          | Pique    | 34    | FC Barcelona

[PlayerCode] | [Key]    | [Value]
142          | Name     | Messi
142          | Age      | 34 
142          | Club     | PSG
333          | Name     | Ronaldo
333          | Age      | 36
333          | Club     | Manchester United
532          | Name     | Pique
532          | Age      | 34
532          | Club     | FC Barcelona

I want to convert the first table to the second key/value table by using SQL Server. I assume that the 'unpivot' function will be used to do the conversion, but I don't know how..

Does anyone know how to do this?

Answer 1

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.