能否在SQL Server的GROUP BY ROLLUP()查询中使用STRING_AGG()函数？

不能直接使用，因为STRING_AGG不支持ROLLUP/CUBE/GROUPING SETS所需的子聚合合并机制，执行时会触发如下报错：

在CUBE、ROLLUP或GROUPING SET查询中使用的聚合函数必须支持子聚合合并。解决此问题，请移除该聚合函数或使用UNION ALL结合GROUP BY子句编写查询。

原因说明 #

ROLLUP这类分组需要聚合函数具备将低层级分组的聚合结果，合并为更高层级（比如总计）结果的能力。例如COUNT(*)可以把每个用户的角色数量相加得到总数量，但STRING_AGG的合并逻辑没有统一标准（比如是否要把所有用户的角色列表拼接成一个大字符串），因此SQL Server不支持在ROLLUP中直接调用它。

解决方案 #

方案1：用UNION ALL手动拼接总计行

先查询用户级的聚合结果，再单独查询总计行，最后用UNION ALL合并，完全规避ROLLUP对STRING_AGG的要求：

DROP TABLE IF EXISTS RollupTest
CREATE TABLE RollupTest (UserId INT, RoleName VARCHAR(20));
INSERT RollupTest VALUES (1, 'Boss'), (1, 'Dogsbody'), (2, 'Dogsbody'), (2, 'another'), (2, 'parent'), (3, 'another')

-- 用户级聚合
SELECT 
    UserId, 
    NumRoles = COUNT(*), 
    RoleNames = STRING_AGG(RoleName, ', ')
FROM RollupTest
GROUP BY UserId

UNION ALL

-- 总计行
SELECT 
    NULL AS UserId, 
    COUNT(*) AS NumRoles, 
    NULL AS RoleNames
FROM RollupTest
ORDER BY UserId; -- 确保总计行在最后

执行后得到预期结果：

UserId      NumRoles    RoleNames
----------- ----------- -------------------------
1           2           Boss, Dogsbody
2           3           Dogsbody, another, parent
3           1           another
NULL        6           NULL

方案2：先子查询聚合，再做ROLLUP

先通过子查询得到每个用户的角色列表和角色数量，再对这个结果集做ROLLUP，此时仅对NumRoles做聚合，RoleNames在总计行设为NULL：

DROP TABLE IF EXISTS RollupTest
CREATE TABLE RollupTest (UserId INT, RoleName VARCHAR(20));
INSERT RollupTest VALUES (1, 'Boss'), (1, 'Dogsbody'), (2, 'Dogsbody'), (2, 'another'), (2, 'parent'), (3, 'another')

SELECT 
    UserId, 
    NumRoles = SUM(NumRoles), 
    RoleNames = CASE WHEN GROUPING(UserId) = 0 THEN RoleNames ELSE NULL END
FROM (
    -- 先计算用户级的聚合结果
    SELECT 
        UserId, 
        NumRoles = COUNT(*), 
        RoleNames = STRING_AGG(RoleName, ', ')
    FROM RollupTest
    GROUP BY UserId
) AS UserRoles
GROUP BY ROLLUP(UserId);

该方法既利用了ROLLUP的分组逻辑，又避免了在ROLLUP中直接使用STRING_AGG，同样能得到符合预期的输出。

关于GROUPING无效的说明 #

即使在SELECT中用GROUPING(UserId)判断是否为总计行，SQL Server在执行ROLLUP分组时，依然会尝试对所有聚合函数（包括STRING_AGG）执行子聚合合并操作，因此无法通过这种方式绕过报错，必须从查询结构上调整。

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