如何在SQL员工缺勤透视表（Pivot）中过滤NULL值？

你的问题根源很明确：源数据里存在Bezeichnung为空的记录，在透视后这些空值就转化成了NULL。下面给你三种针对性的解决方案，根据你的实际需求选择：

方案1：从源数据过滤空值（推荐） #

如果这些Bezeichnung为空的记录本身就不需要出现在结果里，最直接的方式是在子查询中提前过滤掉它们。修改动态SQL里的子查询部分，加入对Bezeichnung的非空判断：

DECLARE @columns NVARCHAR(MAX) = '', @sql NVARCHAR(MAX) = '';

-- 保持列名生成逻辑不变
SELECT @columns = STUFF((SELECT ',' + QUOTENAME(Datum) 
                         FROM (SELECT DISTINCT Datum FROM table1 WHERE Datum > DATEADD(day, -1, GETDATE()) AND Datum < DATEADD(day, +13, GETDATE())) sub 
                         ORDER BY Datum FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 修改子查询，过滤空的Bezeichnung
SET @sql =' 
SELECT * 
FROM ( 
    SELECT DISTINCT Vorname, Nachname, Bezeichnung, datum 
    FROM table1 a 
    LEFT JOIN table2 b ON a.Mitarbeiter_ID = b.Mitarbeiter_ID
    -- 过滤掉Bezeichnung为NULL或空字符串的行
    WHERE ISNULL(b.Bezeichnung, '''') <> ''''
) t 
PIVOT (MAX(Bezeichnung) FOR datum IN ('+ @columns +') ) AS pivot_table;';

EXECUTE sp_executesql @sql;

这样一来，只有Bezeichnung有有效值的记录会被纳入透视，结果里自然不会出现NULL。

方案2：将NULL替换为空字符串或自定义文本 #

如果你需要保留行，但不想显示NULL，可以在动态生成的列中用ISNULL或COALESCE把NULL替换成空字符串（或者你想要的文本，比如'-'）。需要修改列名的生成逻辑：

DECLARE @columns NVARCHAR(MAX) = '', @sql NVARCHAR(MAX) = '';

-- 修改列生成逻辑，给每个列套上ISNULL处理
SELECT @columns = STUFF((SELECT ', ISNULL(' + QUOTENAME(Datum) + ', '''') AS ' + QUOTENAME(Datum) 
                         FROM (SELECT DISTINCT Datum FROM table1 WHERE Datum > DATEADD(day, -1, GETDATE()) AND Datum < DATEADD(day, +13, GETDATE())) sub 
                         ORDER BY Datum FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 提取原始列名用于PIVOT的IN子句
DECLARE @pivot_columns NVARCHAR(MAX) = REPLACE(REPLACE(@columns, 'ISNULL(', ''), ', '''') AS ', ', ')

-- 动态SQL中选择处理后的列，而非直接SELECT *
SET @sql =' 
SELECT Vorname, Nachname, ' + @columns + '
FROM ( 
    SELECT DISTINCT Vorname, Nachname, Bezeichnung, datum 
    FROM table1 a LEFT JOIN table2 b ON a.Mitarbeiter_ID = b.Mitarbeiter_ID
) t 
PIVOT (MAX(Bezeichnung) FOR datum IN ('+ @pivot_columns +') ) AS pivot_table;';

EXECUTE sp_executesql @sql;

这个方案会把所有NULL值替换成空字符串，结果看起来更整洁。

方案3：过滤透视后全为NULL的行 #

如果你的需求是保留有至少一个有效日期值的行，过滤掉所有日期列都是NULL的行，可以在透视后加WHERE条件：

DECLARE @columns NVARCHAR(MAX) = '', @sql NVARCHAR(MAX) = '';

SELECT @columns = STUFF((SELECT ',' + QUOTENAME(Datum) 
                         FROM (SELECT DISTINCT Datum FROM table1 WHERE Datum > DATEADD(day, -1, GETDATE()) AND Datum < DATEADD(day, +13, GETDATE())) sub 
                         ORDER BY Datum FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 生成COALESCE条件，只要有一个列不为NULL就保留该行
DECLARE @filter NVARCHAR(MAX) = 'COALESCE(' + REPLACE(@columns, ', ', ', ') + ') IS NOT NULL';

SET @sql =' 
SELECT * 
FROM ( 
    SELECT DISTINCT Vorname, Nachname, Bezeichnung, datum 
    FROM table1 a LEFT JOIN table2 b ON a.Mitarbeiter_ID = b.Mitarbeiter_ID
) t 
PIVOT (MAX(Bezeichnung) FOR datum IN ('+ @columns +') ) AS pivot_table
WHERE ' + @filter + ';';

EXECUTE sp_executesql @sql;

这个方案会剔除那些在所有日期列都没有有效Bezeichnung的行。

内容的提问来源于stack exchange，提问作者BM-SMS