如何结合STUFF与PIVOT实现SQL动态列员工ID聚合

原始数据 #

需求说明 #

使用PIVOT命令将Division字段转换为列，列值为对应分组下逗号分隔的EmployeeID字符串；按GroupID和TaxID分组，对动态生成的Division列使用string_agg()函数聚合。

期望输出 #

解决方案 #

静态PIVOT实现（已知Division值） #

如果Division的取值固定（比如仅ABC、DEF），可以直接写死列名，先通过string_agg()完成聚合，再用PIVOT转置：

SELECT 
    GroupID,
    TaxID,
    ISNULL(ABC, '') AS ABC,
    ISNULL(DEF, '') AS DEF
FROM (
    -- 先按GroupID、TaxID、Division分组，聚合EmployeeID为逗号分隔字符串
    SELECT 
        GroupID,
        TaxID,
        Division,
        STRING_AGG(EmployeeID, ',') AS EmployeeIDs
    FROM YourTableName
    GROUP BY GroupID, TaxID, Division
) AS AggregatedData
PIVOT (
    MAX(EmployeeIDs) -- PIVOT需指定聚合函数，此处取唯一值即可
    FOR Division IN ([ABC], [DEF])
) AS PivotedData
ORDER BY GroupID, TaxID;

动态PIVOT实现（Division值不固定） #

如果Division的取值是动态变化的，需要用动态SQL自动生成列名：

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- 生成所有Division的列名，用方括号包裹避免语法冲突
SELECT @Columns = STRING_AGG(QUOTENAME(Division), ', ')
FROM (SELECT DISTINCT Division FROM YourTableName) AS DistinctDivisions;

-- 拼接动态SQL语句
SET @SQL = N'
SELECT 
    GroupID,
    TaxID,
    ' + @Columns + '
FROM (
    SELECT 
        GroupID,
        TaxID,
        Division,
        STRING_AGG(EmployeeID, '', '') AS EmployeeIDs
    FROM YourTableName
    GROUP BY GroupID, TaxID, Division
) AS AggregatedData
PIVOT (
    MAX(EmployeeIDs)
    FOR Division IN (' + @Columns + ')
) AS PivotedData
ORDER BY GroupID, TaxID;';

-- 执行动态SQL
EXEC sp_executesql @SQL;

说明：这里无需使用STUFF，因为STRING_AGG()已经直接生成了逗号分隔的字符串。先完成分组聚合，再用PIVOT转置即可，避免了和FOR XML PATH拼接字符串的逻辑混淆。

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