SQL Server 2016多表关联下STRING_AGG的替代实现方案

问题场景 #

使用SQL Server 2016，需按#userTable的id和username分组，获取关联#roleTable的roleDesc并以逗号分隔。#userTable与#roleTable通过#mapTable映射，单个用户可拥有多个角色。由于SQL Server 2016不支持STRING_AGG函数，尝试用STUFF实现时触发错误：

Column '#roleTable.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

错误原因分析 #

原代码的核心问题是子查询关联了外层已分组的#roleTable R，但分组后外层的R.id不在聚合函数或GROUP BY子句中，违反了SQL分组查询的语法规则。正确的思路应该是在子查询中直接通过用户ID关联映射表和角色表，而非依赖外层的角色表关联。

可行解决方案 #

调整子查询逻辑，直接基于当前用户ID查询其所有角色，再通过STUFF+FOR XML PATH完成字符串拼接：

IF(OBJECT_ID('Tempdb..#userTable') IS NOT NULL) 
    DROP TABLE #userTable

CREATE TABLE #userTable
(
    id int, 
    username nvarchar(200)
) 

INSERT INTO #userTable 
VALUES (1, 'user1'), 
       (2, 'user2') 
   
IF (OBJECT_ID('Tempdb..#roleTable') IS NOT NULL) 
    DROP TABLE #roleTable

CREATE TABLE #roleTable
(
    id int, 
    roleDesc nvarchar(200)
) 

INSERT INTO #roleTable 
VALUES (1, 'role1'), 
       (2, 'role2'), 
       (3, 'role3') 
  
IF(OBJECT_ID('Tempdb..#mapTable') IS NOT NULL) 
    DROP TABLE #mapTable

CREATE TABLE #mapTable
(
    id int, 
    map_userId int, 
    map_roleId int
) 

INSERT INTO #mapTable 
VALUES (1, 1, 1), 
       (2, 1, 2), 
       (3, 2, 2)
  
SELECT 
    U.id, 
    U.username,
    STUFF((
        SELECT ',' + R.roleDesc
        FROM #mapTable MAP
        JOIN #roleTable R ON R.id = MAP.map_roleId
        WHERE MAP.map_userId = U.id
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS roleDesc
FROM #userTable U
GROUP BY U.id, U.username

关键说明 #

• 子查询通过MAP.map_userId = U.id直接关联当前用户，确保只查询该用户所属的角色
• 使用TYPE参数和.value('.', 'NVARCHAR(MAX)')可以避免特殊字符（如&、<、>）被XML转义
• STUFF函数用于移除拼接结果开头多余的逗号，得到符合要求的格式

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