SQL 動態行列轉換中將 NULL 值轉為 0 的實現方案

問題背景

使用 PIVOT 進行動態行列轉換時，課程列不固定，無法在靜態 SQL 中對每列使用 ISNULL(col, 0)，導致後續聚合計算（如平均分）忽略 NULL 行，結果偏差。

方案一：臨時表 + 觸發器預處理（推薦）

1. 創建臨時報表表，包含所有可能課程列，預設值為 0。
2. 編寫 AFTER INSERT/UPDATE 觸發器，將源表數據同步至臨時表，同時將 NULL 轉為 0。
3. 報表直接查詢臨時表，無需處理 NULL。

方案二：動態 SQL 構建 COALESCE

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(課程) + ' = COALESCE(' + QUOTENAME(課程) + ', 0)', ',')
FROM (SELECT DISTINCT 課程 FROM tb) t;
SET @sql = 'SELECT 姓名, 學號, ' + @cols + ' FROM (SELECT * FROM tb) src PIVOT (MAX(分數) FOR 課程 IN (' + @cols + ')) pvt';
EXEC sp_executesql @sql;

方案三：應用層處理

在 Java/C# 等應用代碼中獲取 ResultSet 後，遍歷列將 null 替換為 0，再進行計算。靈活但增加應用層邏輯。

建議

若報表頻繁生成且性能敏感，方案一（觸發器維護冗余表）查詢最快；若課程變動極頻繁，方案二（動態 SQL）維護成本較低。