SQL Server 2019按季度聚合销售代表评级至单行的技术问询

核心思路 #

先按销售代表（rep）和季度（quartc）分组，将每个季度内的评级拼接成逗号分隔的字符串；再按rep聚合，把各季度的结果合并成单行，同时保留季度分组的结构。

方案1：实现更优输出格式（Qtr: ratings） #

使用SQL Server 2019原生支持的STRING_AGG函数，语法简洁且性能更优：

WITH QuarterlyRatings AS (
    SELECT 
        rep,
        quartc,
        -- 按季度内的row顺序拼接评级
        STRING_AGG(rate, ',') WITHIN GROUP (ORDER BY [row]) AS qtr_ratings
    FROM RepRatings
    GROUP BY rep, quartc
)
SELECT 
    rep,
    -- 按季度顺序拼接成最终的结构化字符串
    STRING_AGG(CONCAT(quartc, ': ', qtr_ratings), ' ') WITHIN GROUP (ORDER BY quartc) AS [Qtr: ratings]
FROM QuarterlyRatings
GROUP BY rep;

输出结果：

rep      |   Qtr: ratings
------   |   ----------------------------------------
911911   |   Q1M: 1,1,2,2,2,1 Q2M:2,2,2,2,1,1 Q3M:2,2,2,1,2

方案2：实现分栏输出格式 #

如果需要拆分ratings和quartc两列的格式，可调整如下：

WITH QuarterlyRatings AS (
    SELECT 
        rep,
        quartc,
        STRING_AGG(rate, ',') WITHIN GROUP (ORDER BY [row]) AS qtr_ratings
    FROM RepRatings
    GROUP BY rep, quartc
)
SELECT 
    rep,
    -- 用|分隔各季度评级
    STRING_AGG(qtr_ratings, ' | ') WITHIN GROUP (ORDER BY quartc) AS ratings,
    -- 用|分隔各季度标识
    STRING_AGG(quartc, ' | ') WITHIN GROUP (ORDER BY quartc) AS quartc
FROM QuarterlyRatings
GROUP BY rep;

输出结果：

rep      |   ratings                           |      quartc
------   |   -------------------------------   |      ---------------
911911   |   1,1,2,2,2,1 | 2,2,2,2,1,1 | 2,2,2,1,2   |      Q1M | Q2M | Q3M

兼容旧版本的FOR XML方案 #

如果需要兼容SQL Server 2019以下版本，可使用传统的FOR XML PATH写法：

WITH QuarterlyRatings AS (
    SELECT 
        rep,
        quartc,
        -- 拼接单个季度的评级
        ratings = STUFF((
            SELECT ',' + rate 
            FROM RepRatings b 
            WHERE b.rep = a.rep AND b.quartc = a.quartc
            ORDER BY b.[row]
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    FROM RepRatings a
    GROUP BY rep, quartc
)
SELECT 
    rep,
    -- 拼接所有季度的结构化字符串
    [Qtr: ratings] = STUFF((
        SELECT ' ' + CONCAT(quartc, ': ', ratings)
        FROM QuarterlyRatings b
        WHERE b.rep = a.rep
        ORDER BY b.quartc
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM QuarterlyRatings a
GROUP BY rep;

关键注意事项 #

• 必须先按rep+quartc分组聚合单季度评级，再全局聚合，才能保证评级按季度分组；
• 使用WITHIN GROUP (ORDER BY)（或FOR XML中的ORDER BY）确保评级顺序与原始数据的row字段一致，季度顺序按quartc自然排序（Q1M→Q2M→Q3M）。

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