unpivot未获取空值

xoshrz7s 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(358)

我有以下几点：

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

sql sql-server tsql

来源：https://stackoverflow.com/questions/62574208/unpivot-not-getting-null-values

2条答案

按热度按时间

55ooxyrt1#

例如，使用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;

赞(0）回复(0）举报 2021-07-24

daupos2t2#

这被认为是 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);

赞(0）回复(0）举报 2021-07-24

我来回答

unpivot未获取空值

2条答案

相关问题

热门标签

最新问答