TSQL无聚合场景下使用PIVOT实现行转列的解决方案问询

方案1：使用PIVOT实现 #

PIVOT语法强制要求填写聚合函数，由于你的数据中同一个Record No下每个Property仅对应唯一的Detail值，所以可以使用MAX()或者MIN()作为聚合函数，二者返回的都是原值，不会改变查询结果，刚好能满足你不需要实际聚合的场景。
示例代码：

SELECT 
    [Record No],
    [Title],
    [First Name],
    [Surname] AS [Second Name],
    [Gender]
FROM 
    Table1
PIVOT
(
    MAX(Detail) -- 每组仅一个值，MAX/MIN效果一致，仅用于满足PIVOT语法要求
    FOR Property IN ([Title], [First Name], [Surname], [Gender])
) AS PivotTable
ORDER BY [Record No]

方案2：使用条件聚合实现（更灵活易读） #

如果你觉得PIVOT语法不够直观，也可以用GROUP BY + 条件聚合的写法，逻辑更清晰，调整字段也更方便：

SELECT
    [Record No],
    MAX(CASE WHEN Property = 'Title' THEN Detail END) AS Title,
    MAX(CASE WHEN Property = 'First Name' THEN Detail END) AS [First Name],
    MAX(CASE WHEN Property = 'Surname' THEN Detail END) AS [Second Name],
    MAX(CASE WHEN Property = 'Gender' THEN Detail END) AS Gender
FROM Table1
GROUP BY [Record No]
ORDER BY [Record No]

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