sql - UNPIVOT 没有得到 NULL 值

Question

我有以下内容：

SELECT Custid, Shipperid, Freight
FROM Sales.FreightTotals
UNPIVOT( Freight FOR Shipperid IN([1],[2],[3]) ) AS U;

我有以下结果：

custid shipperid freight
1      1         25
1      3         100
2      2         75

获得 NULL 值后我的预期输出：

custid shipperid freight
1      1         25
1      2         NULL
1      3         100
2      1         NULL
2      2         75
2      3         NULL

标签： sqlsql-servertsql

Answer 1

例如，使用 CTE 将 NULL 设为 0.00，然后使用 NULLIF() 将 0.00 替换为 NULL：

WITH C AS
(
SELECT custid,
ISNULL([1], 0.00) AS [1],
ISNULL([2], 0.00) AS [2],
ISNULL([3], 0.00) AS [3]
FROM Sales.FreightTotals
)
SELECT custid, shipperid, NULLIF(freight, 0.00) AS freight
FROM C
UNPIVOT( freight FOR shipperid IN([1],[2],[3]) ) AS U;