如何将RequestComments的5条记录转为同一行多列（SQL实现）

表结构与数据 #

Requests表 #

RequestComments表 #

原有问题查询 #

以下查询会生成重复行，无法满足单行展示多条评论的需求：

select
    r.[Reference],
    c.DateCreated,
    c.[Sender],
    c.[Comment],
    r.[Client Surname],
    r.[Client Address]
from Requests r
cross apply (
    select top 5
        rc.DateCreated,
        rc.[Sender],
        rc.[Comment]
    from RequestComments rc
    where rc.[Reference] = r.[RequestRef]
    order by rc.DateCreated
) c

需求 #

将每个Request对应的最多5条评论的DateCreated、Sender、Comment字段转为同一行的不同列，最终结果无重复行，期望输出样式如下：

解决方案 #

通过给每条评论编号，结合条件聚合实现单行多列转换：

SELECT
    r.[Reference],
    -- 第1条评论字段
    MAX(CASE WHEN rn = 1 THEN rc.DateCreated END) AS DateCreated1,
    MAX(CASE WHEN rn = 1 THEN rc.Sender END) AS Sender1,
    MAX(CASE WHEN rn = 1 THEN rc.Comment END) AS Comment1,
    -- 第2条评论字段
    MAX(CASE WHEN rn = 2 THEN rc.DateCreated END) AS DateCreated2,
    MAX(CASE WHEN rn = 2 THEN rc.Sender END) AS Sender2,
    MAX(CASE WHEN rn = 2 THEN rc.Comment END) AS Comment2,
    -- 第3条评论字段
    MAX(CASE WHEN rn = 3 THEN rc.DateCreated END) AS DateCreated3,
    MAX(CASE WHEN rn = 3 THEN rc.Sender END) AS Sender3,
    MAX(CASE WHEN rn = 3 THEN rc.Comment END) AS Comment3,
    -- 第4条评论字段
    MAX(CASE WHEN rn = 4 THEN rc.DateCreated END) AS DateCreated4,
    MAX(CASE WHEN rn = 4 THEN rc.Sender END) AS Sender4,
    MAX(CASE WHEN rn = 4 THEN rc.Comment END) AS Comment4,
    -- 第5条评论字段
    MAX(CASE WHEN rn = 5 THEN rc.DateCreated END) AS DateCreated5,
    MAX(CASE WHEN rn = 5 THEN rc.Sender END) AS Sender5,
    MAX(CASE WHEN rn = 5 THEN rc.Comment END) AS Comment5,
    r.[Client Surname],
    r.[Client Address]
FROM Requests r
LEFT JOIN (
    SELECT
        RequestRef,
        DateCreated,
        Sender,
        Comment,
        -- 按RequestRef分组，给评论按创建日期排序编号
        ROW_NUMBER() OVER(PARTITION BY RequestRef ORDER BY DateCreated) AS rn
    FROM RequestComments
) rc ON r.[Reference] = rc.RequestRef
WHERE rn <= 5 -- 限制最多5条评论
GROUP BY r.[Reference], r.[Client Surname], r.[Client Address]
ORDER BY r.[Reference]

关键说明 #

• 子查询中用ROW_NUMBER()给每个Request下的评论按创建日期排序并分配序号（1-5）
• 主查询通过CASE WHEN和MAX()聚合，将不同序号的评论字段映射到对应列
• 使用LEFT JOIN保证无评论的Request也能被输出，若仅需有评论的Request可替换为INNER JOIN
• 最终每个Request仅生成一行数据，无重复

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