SQL Server实现PIVOT行转列，合并人员异动记录为单行

原代码问题分析 #

• PIVOT逻辑错误：聚合字段选择了id，实际需要聚合的是Place；FOR子句指定的列错误，应该基于异动记录的顺序序号生成列，而非id
• 未对同一用户的异动记录按时间排序生成唯一标识，无法区分Place1、Place2等历史地点的先后顺序

正确实现方案 #

先通过ROW_NUMBER()为每个用户的异动记录按时间升序生成顺序序号，再基于该序号进行PIVOT转列，同时通过MAX()函数获取最后一次异动时间。

完整SQL代码 #

WITH ranked_data AS (
    SELECT 
        a.id_mcm,
        b.nama_mcm AS Name,
        c.nama_ctr AS Place,
        a.tgl_mutasi AS Move_On,
        -- 按用户分组，异动时间升序生成序号，标记地点顺序
        ROW_NUMBER() OVER (PARTITION BY a.id_mcm ORDER BY a.tgl_mutasi ASC) AS Place_Seq
    FROM mcmmutasi AS a
    LEFT JOIN mcmmcm AS b ON b.id_mcm = a.id_mcm
    LEFT JOIN mcmcenter AS c ON c.kode_ctr = a.kode_ctr
),
pivot_result AS (
    SELECT 
        id_mcm,
        Name,
        [1] AS Place1,
        [2] AS Place2,
        [3] AS Place3,
        [4] AS Place4,
        -- 取最大异动时间作为最后一次异动记录时间
        MAX(Move_On) AS [Move_On(Last One)]
    FROM ranked_data
    PIVOT (
        MAX(Place) -- 聚合地点字段
        FOR Place_Seq IN ([1], [2], [3], [4]) -- 基于序号转成多列
    ) AS p
    GROUP BY id_mcm, Name, [1], [2], [3], [4]
)
SELECT * FROM pivot_result;

代码关键点说明 #

1. ranked_data CTE：为每个用户的异动记录按时间排序生成Place_Seq，确保Place1是最早的异动地点，序号递增对应地点异动的先后顺序
2. pivot_result CTE：通过PIVOT将多行地点记录转成Place1到Place4的单列，同时用MAX(Move_On)直接获取该用户的最后一次异动时间
3. 最终查询输出每个用户的单行记录，包含所有历史异动地点和最后异动时间

测试数据执行结果 #

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