处理 UNPIVOT 中的 NULL 值 [英] Handle NULL value in UNPIVOT

问题描述

我可以对表进行逆透视，但结果中不包含空值.

I'm able to unpivot a table but null values are not included in the result.

create table pivot_task
(
age int null,
[a] numeric(8,2),
[b] numeric(8,2),
[c] numeric(8,2),
[d] numeric(8,2),
[e] numeric(8,2)
);

select * from pivot_task;

insert into pivot_task values (18, 0.5, null, 0.6, 1.21, 1.52),
(19, 7.51, 6.51, 5.51, null, 3.53),
(20, 4.52, 4.52, 6.52, 3.53, null);


select age, [over], [av]
from pivot_task
unpivot
(
 [av]
 for [over] in ([a], [b], [c], [d], [e])
) a;

您可以在 http://sqlfiddle.com/#!6/2ab59/1 上查看结果对于 18 岁 [over] b 并且它的 null 值丢失，我想在每次遇到 null 时也包含 null.

You can see the result on http://sqlfiddle.com/#!6/2ab59/1 for 18 age [over] b and its null value is missing I want to include null as well for every null encounter.

我发现用不同的值替换 null 然后替换所有那些不变的不同值的方法对我的工作来说是不可行的.我只想包含在逆透视中.

I found replacing null with different value and then replacing all those constant different value approach is not feasible for my work. I want to include in unpivot only.

推荐答案

这很丑陋，但并不依赖于必须为 NULL 找到带外替换:

This is ugly but doesn't rely on having to find an out-of-band replacement for NULL:

declare @pivot_task table
(
age int null,
[a] numeric(8,2),
[b] numeric(8,2),
[c] numeric(8,2),
[d] numeric(8,2),
[e] numeric(8,2)
);

insert into @pivot_task values (18, 0.5, null, 0.6, 1.21, 1.52),
(19, 7.51, 6.51, 5.51, null, 3.53),
(20, 4.52, 4.52, 6.52, 3.53, null);


select a.age, pmu.[over], [av]
from (select 'a' as [over] union all select 'b' union all select 'c'
        union all select 'd' union all select 'e') pmu
cross join (select age from @pivot_task) as a
left join
@pivot_task pt
unpivot
(
 [av]
 for [over] in ([a], [b], [c], [d], [e])
) ex
on pmu.[over] = ex.[over] and
   a.age = ex.age

结果:

age         over av
----------- ---- ---------------------------------------
18          a    0.50
18          b    NULL
18          c    0.60
18          d    1.21
18          e    1.52
19          a    7.51
19          b    6.51
19          c    5.51
19          d    NULL
19          e    3.53
20          a    4.52
20          b    4.52
20          c    6.52
20          d    3.53
20          e    NULL

但是如果你沿着这条路走下去，你可以完全消除UNPIVOT:

But if you're going down this route, you can eliminate the UNPIVOT entirely:

select a.age, pmu.[over],
      CASE pmu.[over]
           WHEN 'a' THEN a.a
           WHEN 'b' THEN a.b
           WHEN 'c' THEN a.c
           WHEN 'd' THEN a.d
           WHEN 'e' THEN a.e
        END [av]
from (select 'a' as [over] union all select 'b' union all select 'c'
        union all select 'd' union all select 'e') pmu
cross join @pivot_task as a

这篇关于处理 UNPIVOT 中的 NULL 值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！