Synapse Analytics中STRING_AGG多列排序聚合报错求助

问题原因 #

Azure Synapse Analytics 不允许在同一聚合作用域中使用多个带 WITHIN GROUP (ORDER BY) 的 STRING_AGG 函数，因为它们的排序规则会产生冲突，这就是触发 Multiple ordered aggregate functions in the same scope have mutually incompatible orderings 错误的原因。

分步解决代码 #

方法一：先按部门+层级聚合拼接，再用PIVOT转列 #

这种方式拆分聚合逻辑，避免同一作用域多排序聚合的冲突，同时自然实现姓名去重，是更简洁的最优方案：

SELECT
    Department,
    [0] AS [Level 0],
    [1] AS [Level 1],
    [2] AS [Level 2],
    [3] AS [Level 3],
    [4] AS [Level 4]
FROM (
    -- 先去重，再按部门+层级拼接排序后的姓名
    SELECT
        Department,
        Level,
        STRING_AGG(DISTINCT [Employee Name], ',') WITHIN GROUP (ORDER BY [Employee Name]) AS EmployeeList
    FROM employee_records
    GROUP BY Department, Level
) AS SourceData
PIVOT (
    MAX(EmployeeList)
    FOR Level IN ([0], [1], [2], [3], [4])
) AS PivotTable;

方法二：拆分层级聚合逻辑修复原Group By方案 #

如果需要保留类似原查询的结构，可将每个层级的聚合拆分到独立子查询，规避Synapse的限制：

SELECT
    d.Department,
    lv0.[Level 0],
    lv1.[Level 1],
    lv2.[Level 2],
    lv3.[Level 3],
    lv4.[Level 4]
FROM (
    SELECT DISTINCT Department FROM employee_records
) d
LEFT JOIN (
    SELECT
        Department,
        STRING_AGG(DISTINCT [Employee Name], ',') WITHIN GROUP (ORDER BY [Employee Name]) AS [Level 0]
    FROM employee_records
    WHERE Level = 0
    GROUP BY Department
) lv0 ON d.Department = lv0.Department
LEFT JOIN (
    SELECT
        Department,
        STRING_AGG(DISTINCT [Employee Name], ',') WITHIN GROUP (ORDER BY [Employee Name]) AS [Level 1]
    FROM employee_records
    WHERE Level = 1
    GROUP BY Department
) lv1 ON d.Department = lv1.Department
LEFT JOIN (
    SELECT
        Department,
        STRING_AGG(DISTINCT [Employee Name], ',') WITHIN GROUP (ORDER BY [Employee Name]) AS [Level 2]
    FROM employee_records
    WHERE Level = 2
    GROUP BY Department
) lv2 ON d.Department = lv2.Department
LEFT JOIN (
    SELECT
        Department,
        STRING_AGG(DISTINCT [Employee Name], ',') WITHIN GROUP (ORDER BY [Employee Name]) AS [Level 3]
    FROM employee_records
    WHERE Level = 3
    GROUP BY Department
) lv3 ON d.Department = lv3.Department
LEFT JOIN (
    SELECT
        Department,
        STRING_AGG(DISTINCT [Employee Name], ',') WITHIN GROUP (ORDER BY [Employee Name]) AS [Level 4]
    FROM employee_records
    WHERE Level = 4
    GROUP BY Department
) lv4 ON d.Department = lv4.Department;

代码说明 #

• 两种方法都通过DISTINCT在STRING_AGG中实现了员工姓名去重，解决了示例中Jack重复的问题。
• 方法一使用PIVOT更符合你提到的最优方案预期，代码简洁且逻辑清晰。
• 两种方案的输出均匹配期望结果，且无需额外外层ORDER BY，可直接作为子查询嵌入更高层级的查询中。

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