Can we use SQL Server STRING_AGG() in queries using GROUP BY ROLLUP ()

TL;DR: Can we use STRING_AGG aggregate function in a query using GROUP BY ROLLUP(...)?

I am converting old code that used the STUFF( (SELECT...FOR XML) ) approach, so that it uses STRING_AGG instead.

I cannot seem to use STRING_AGG when using ROLLUP, as I get the error

Aggregate functions that are used with CUBE, ROLLUP, or GROUPING SET queries must provide for the merging of subaggregates. To fix this problem, remove the aggregate function or write the query using UNION ALL over GROUP BY clauses.

Code that works, just using GROUP BY ....

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
/* output is...
UserId      NumRoles    RoleNames
----------- ----------- -------------------------
1           2           Boss, Dogsbody
2           3           Dogsbody, another, parent
3           1           another
*/

Change to use ROLLUP to get a total row, expecting to get NULL for the aggregated names column in the total row...

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 ROLLUP(UserId)        --<<< just added ROLLUP here

/* expected output to be something like the following...
UserId      NumRoles    RoleNames
----------- ----------- -------------------------
1           2           Boss, Dogsbody
2           3           Dogsbody, another, parent
3           1           another
NULL        6           NULL

*/

... and we get the error. I've tried using GROUPING(...) to try and avoid invoking the STRING_AGG() on the totals line and it makes no difference.

I guess it's obvious and maybe STRING_AGG() cannot be rolled up for a sensible reason, but I can't see it. I don't find the error message helpful, but I could just be a bit dense. The old approach using FOR XML works with ROLLUP, of course.

Solution

As you have seen, STRING_AGG is not allowed when using any kind of grouping set in a GROUP BY.

You can hack it as follows

SELECT
  UserId,
  NumRoles = SUM(NumUsers),
  RoleNames = STRING_AGG(RoleName, ', ')
FROM (
    SELECT
      UserId,
      RoleName,
      NumUsers = COUNT(*),
      IsGrouped = GROUPING(UserId)
    FROM RollupTest rt
    GROUP BY GROUPING SETS (
        (UserId, RoleName),
        (RoleName)
    )
) rt
GROUP BY
  IsGrouped,
  UserId
ORDER BY
  IsGrouped,
  UserId;

db<>fiddle

The idea here is as follows:

Use GROUPING SETS in a derived subquery to break out the rollup rows into separate rows of UserId, NULL. Calculate a total count at this level. You also still get all the original rows.
Group on the outside by UserId and IsGrouped
Return the sum of the count previously calculated, as well as the aggregated strings.
You can use the IsGrouped column for sorting and conditionals, but this is not strictly necessary.

Note that no self-joins or window functions were used in this solution, although it does require a second sort.