如何基于前缀对SQL查询结果逆透视，实现同表两行数据对比？

方法一：使用UNION ALL（字段较少时推荐） #

这种方式逻辑直观，直接将每个字段拆分为单独行返回：

SELECT 'Addressee' AS Field, OLD.[Addressee] AS OLD, NEW.[Addressee] AS NEW
FROM [dbo].[Location] OLD, [dbo].[Location] NEW
WHERE OLD.[ID] = 590230 AND NEW.[ID] = 1
UNION ALL
SELECT 'BuildingName' AS Field, OLD.[BuildingName] AS OLD, NEW.[BuildingName] AS NEW
FROM [dbo].[Location] OLD, [dbo].[Location] NEW
WHERE OLD.[ID] = 590230 AND NEW.[ID] = 1

方法二：使用UNPIVOT（字段较多时推荐） #

通过UNPIVOT转换宽表结构，减少重复的查询条件，适合字段数量较多的场景：

WITH CTE AS (
    SELECT 
        OLD.[Addressee] OLD_Addressee, NEW.[Addressee] NEW_Addressee, 
        OLD.[BuildingName] OLD_BuildingName, NEW.[BuildingName] NEW_BuildingName
    FROM [dbo].[Location] OLD, [dbo].[Location] NEW
    WHERE OLD.[ID] = 590230 AND NEW.[ID] = 1
)
SELECT 
    REPLACE(OLD_Field, 'OLD_', '') AS Field,
    OLD_Value AS OLD,
    NEW_Value AS NEW
FROM CTE
UNPIVOT (OLD_Value FOR OLD_Field IN (OLD_Addressee, OLD_BuildingName)) AS UP1
JOIN CTE
UNPIVOT (NEW_Value FOR NEW_Field IN (NEW_Addressee, NEW_BuildingName)) AS UP2
ON REPLACE(UP1.OLD_Field, 'OLD_', '') = REPLACE(UP2.NEW_Field, 'NEW_', '')

两种方法均可输出你需要的窄表格式：

内容的提问来源于stack exchange，提问作者Meg