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