SQL通过桥表关联事实与维度表、拼接目标列避免一对多及聚合报错

报错原因 #

你出现is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause报错的核心原因是：当查询中使用了聚合函数（包括行拼接类的聚合逻辑）时，所有未被聚合函数包裹的查询列，都必须在GROUP BY子句中声明。你之前的写法应该是直接在主查询中做拼接同时关联了多个表的属性，没有把所有非聚合列都加入GROUP BY才触发了报错。

解决方案 #

推荐先把维度侧的拼接逻辑单独做预聚合生成子查询，再和事实表、其他维度表关联，不需要在主查询写大量GROUP BY字段，也不会出现行数放大的一对多问题。

适用SQL Server 2017及以上版本（用STRING_AGG实现）

SELECT 
    f.dim1Key,
    f.factvalue1,
    f.groupKey,
    d1.attributeTwo,
    d1.attributeThree,
    ISNULL(d2_concat.attributeOne, '') AS attributeOne
FROM fact1 f
-- 关联dim1取属性，一对一关联不会放大行数
INNER JOIN dim1 d1 
    ON f.dim1Key = d1.dim1Key
-- 左关联预聚合后的dim2拼接结果，无匹配的groupKey返回空值
LEFT JOIN (
    SELECT 
        b.groupKey,
        STRING_AGG(d2.attributeOne, ', ') WITHIN GROUP (ORDER BY d2.dim2Key) AS attributeOne
    FROM bridge b
    INNER JOIN dim2 d2 
        ON b.dim2Key = d2.dim2Key
    GROUP BY b.groupKey
) d2_concat
    ON f.groupKey = d2_concat.groupKey

适用SQL Server 2016及更早版本（用FOR XML PATH实现）

替换上方的d2_concat子查询即可：

LEFT JOIN (
    SELECT 
        b.groupKey,
        STUFF((
            SELECT ', ' + d2.attributeOne
            FROM bridge b2
            INNER JOIN dim2 d2 ON b2.dim2Key = d2.dim2Key
            WHERE b2.groupKey = b.groupKey
            ORDER BY d2.dim2Key
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS attributeOne
    FROM bridge b
    GROUP BY b.groupKey
) d2_concat

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