UNPIVOT不获取空值

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

获得空值后的预期输出：

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

Answer 1

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

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;

Answer 2

这被认为是unpivot的一个特性。使用apply代替：

SELECT ft.custid, v.shipperid, v.freight
FROM Sales.FreightTotals ft CROSS APPLY
     (VALUES (1, ft.[1]), (2, ft.[2]), (3, ft.[3])) AS v(shipperid, freight);