如何在TSQL中跨行合并消除NULL值？Azure SQL数据库场景

针对你在PersonWorkload表转置时遇到的问题，核心原因是直接使用PIVOT时的聚合逻辑不符合需求——要么因分组保留GroupID产生大量NULL，要么因移除GroupID导致聚合丢失人员记录。以下是具体的解决方案：

问题根源拆解 #

• 保留GroupID直接PIVOT：PIVOT默认会对GroupID分组，同一GroupID下不同时期的人员记录是分散的，未聚合的情况下，没有对应人员的时期列会显示NULL；同时若同一时期同一岗位有多个人员，聚合函数（如MAX）只会保留单个值，丢失其他人员。
• 移除GroupID后PIVOT：此时PIVOT会基于剩余列（如PersonID）聚合，同样会因MAX等函数只保留单个人员，丢失同岗位同时期的其他记录。

解决方案：先聚合再转置 #

我们需要先将同一GroupID（岗位分组）+时期下的所有人员合并为单个值，再执行PIVOT，同时将NULL替换为空字符串消除冗余。

1. 基础静态SQL示例（适用于已知时期列） #

假设你的PersonWorkload表结构包含GroupID（岗位组ID）、PersonID（人员ID）、Period（时期，如'2024Q1'）、JobTitle（岗位名称）：

-- 先聚合每个岗位组+时期的所有人员
WITH AggregatedData AS (
    SELECT 
        GroupID,
        JobTitle,
        Period,
        -- 用STRING_AGG合并同时期同岗位的人员ID，分隔符可自定义
        STRING_AGG(PersonID, ', ') AS AssignedPersons
    FROM PersonWorkload
    GROUP BY GroupID, JobTitle, Period
)
-- 执行PIVOT并替换NULL为空字符串
SELECT 
    GroupID,
    JobTitle,
    ISNULL([2024Q1], '') AS [2024Q1],
    ISNULL([2024Q2], '') AS [2024Q2],
    ISNULL([2024Q3], '') AS [2024Q3]
    -- 继续添加所有需要的时期列
INTO #TempPivotedResult
FROM AggregatedData
PIVOT (
    MAX(AssignedPersons)
    FOR Period IN ([2024Q1], [2024Q2], [2024Q3])
) AS PivotTable;

-- 查看临时表结果
SELECT * FROM #TempPivotedResult;

2. 动态SQL版本（适配15个时期的场景） #

由于你有15个时间周期，手动编写列名效率极低，用动态SQL自动生成时期列：

-- 生成所有时期的列名（带方括号）
DECLARE @PeriodList NVARCHAR(MAX) = '';
SELECT @PeriodList += QUOTENAME(Period) + ', '
FROM (SELECT DISTINCT Period FROM PersonWorkload) AS Periods
ORDER BY Period;
SET @PeriodList = LEFT(@PeriodList, LEN(@PeriodList) - 2); -- 移除末尾多余逗号

-- 生成带ISNULL处理的选择列
DECLARE @SelectColumns NVARCHAR(MAX) = '';
SELECT @SelectColumns += 'ISNULL(' + QUOTENAME(Period) + ', '''') AS ' + QUOTENAME(Period) + ', '
FROM (SELECT DISTINCT Period FROM PersonWorkload) AS Periods
ORDER BY Period;
SET @SelectColumns = LEFT(@SelectColumns, LEN(@SelectColumns) - 2);

-- 拼接并执行动态PIVOT语句
DECLARE @DynamicSQL NVARCHAR(MAX) = N'
WITH AggregatedData AS (
    SELECT 
        GroupID,
        JobTitle,
        Period,
        STRING_AGG(PersonID, '', '') AS AssignedPersons
    FROM PersonWorkload
    GROUP BY GroupID, JobTitle, Period
)
SELECT 
    GroupID,
    JobTitle,
    ' + @SelectColumns + '
INTO #TempPivotedResult
FROM AggregatedData
PIVOT (
    MAX(AssignedPersons)
    FOR Period IN (' + @PeriodList + ')
) AS PivotTable;';

EXEC sp_executesql @DynamicSQL;

-- 查看结果
SELECT * FROM #TempPivotedResult;

关键说明 #

• STRING_AGG函数：Azure SQL Database 2017及以上版本支持，用于将同一分组下的多个PersonID合并为逗号分隔的字符串，确保所有人员记录都被保留。
• ISNULL替换：将PIVOT后产生的NULL值替换为空字符串，消除冗余的NULL显示。
• 动态SQL：自动适配15个时期的场景，无需手动维护列名，扩展性更强。

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