SQL Server行转列保留重复Rowid记录问题求助

直接使用Pivot或Case+GROUP BY rowid时，聚合函数会合并同一Rowid下的同Key值，导致丢失重复记录。核心解决方案是为每个Rowid下的重复记录生成唯一分组序号，确保每组记录被单独聚合。

模拟测试数据 #

表结构 #

CREATE TABLE dbo.temp (
    rowid INT,
    [key] VARCHAR(50),
    value VARCHAR(50)
);

插入测试数据 #

INSERT INTO dbo.temp (rowid, [key], value)
VALUES
(1, 'Title', 'Mr'),
(1, 'FirstName', 'John'),
(1, 'Title', 'Dr'),
(1, 'FirstName', 'Doe'),
(2, 'Title', 'Ms'),
(2, 'FirstName', 'Jane');

方案1：Case语句+窗口函数 #

通过ROW_NUMBER()为每个Rowid下的同Key值生成序号，再按Rowid+序号分组聚合，保留每组记录：

WITH numbered_temp AS (
    SELECT 
        rowid,
        [key],
        value,
        -- 为同一Rowid+Key下的记录生成唯一序号，确保分组正确
        ROW_NUMBER() OVER (PARTITION BY rowid, [key] ORDER BY (SELECT NULL)) AS group_seq
    FROM dbo.temp
)
SELECT 
    rowid,
    MAX(CASE WHEN [key] = 'Title' THEN value END) AS Title,
    MAX(CASE WHEN [key] = 'FirstName' THEN value END) AS FirstName
    -- 如需其他字段，继续添加对应的CASE语句
FROM numbered_temp
GROUP BY rowid, group_seq
ORDER BY rowid, group_seq;

方案2：Pivot函数+窗口函数 #

同样先添加分组序号，再用Pivot实现行转列：

WITH numbered_temp AS (
    SELECT 
        rowid,
        [key],
        value,
        ROW_NUMBER() OVER (PARTITION BY rowid, [key] ORDER BY (SELECT NULL)) AS group_seq
    FROM dbo.temp
)
SELECT 
    rowid,
    Title,
    FirstName
    -- 如需其他字段，在IN子句中添加对应的Key值
FROM numbered_temp
PIVOT (
    MAX(value)
    FOR [key] IN (Title, FirstName)
) AS pivot_result
ORDER BY rowid, group_seq;

关键说明 #

• 若重复记录有明确的关联顺序（如插入时间、自增ID），请将ORDER BY (SELECT NULL)替换为实际的排序字段（如ORDER BY insert_time），避免序号混乱。
• 聚合函数MAX()可替换为MIN()，因为每组仅对应一条记录，结果一致。

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