Unpivot based on prefix

Unpivot based on prefix

I want to compare two rows in a same table.

SELECT OLD.[Addressee] OLD_Addressee, NEW.[Addressee] NEW_Addressee, 
    OLD.[BuildingName] OLD_BuildingName, NEW.[BuildingName] NEW_BuildigName
FROM [dbo].[Location] OLD,  [dbo].[Location] NEW
WHERE OLD.[ID] = 590230 AND NEW.[ID] = 1

The result is

OLD_Addressee NEW_Addressee OLD_BuildingName NEW_BuildingName
test NULL Buil1 Buil2

I want to convert to this

Field OLD NEW
Addressee test NULL
BuildingName Buil1 Buil2

How can I achieve this? Thanks in advance.

Answer


SELECT
  pvt.*
FROM
  OLD.[Addressee] OLD_Addressee
CROSS JOIN
  NEW.[Addressee] NEW_Addressee, 

    OLD.[BuildingName] OLD_BuildingName, NEW.[BuildingName] NEW_BuildigName

FROM
  [dbo].[Location] OLD
CROSS JOIN
  [dbo].[Location] NEW
OUTER APPLY
(
  VALUES
    ('Addressee',    old.addressee, new.addressee)
    ('BuildingName', old.location,  new.location )
)
  pvt(field, [old], [new]) 
WHERE
      OLD.[ID] = 590230
  AND NEW.[ID] = 1

Enjoyed this article?

Check out more content on our blog or follow us on social media.

Browse more articles