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

[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?

CodePudding user response：

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;

CodePudding user response：

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;

CodePudding user response：

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.