Convert table to key value pair

April 12, 2023

I’m looking to flatten my current table. At the moment I was able to achieve this through an iterative approach but the run time is very poor. I queried the table and then used a cursor to iterate and insert into a new table.

My current table has many many columns and I’m trying to have it flattened. Possibly unpivot.

RecordId    FirstName    LastName    Country     Grade
1           Joe          Fresh       Canada      95
2           John         Smith       Canada      94

The new table would look like this

RecordId    Key        Value
1           FirstName   Joe
1           LastName    Fresh
1           Country     Canada
1           Grade       95
2           FirstName   John
2           LastName    Smith
2           Country     Canada
2           Grade       94

May I have some guidance.

I tried unpivot and was able to flatten the table but I’m missing RecordId column

select [Key], [Value]
from
(
  SELECT * 
  FROM test
) src
unpivot
(
  [Value] for [Key] in ([RecordId],[FirstName],[LastName], [Country], [Grade])
)

>Solution :

Yes, it is unpivoting. Please include the RecordId column in the unpivoted result by adding it to the SELECT statement before the UNPIVOT operation. Try this:

SELECT RecordId, [Key], [Value]
FROM
(
  SELECT RecordId, FirstName, LastName, Country, Grade
  FROM test
) src
UNPIVOT
(
  [Value] FOR [Key] IN (FirstName, LastName, Country, Grade)
) unpvt;