sql – 处理UNPIVOT中的NULL值

create table pivot_task
(
age int null,[a] numeric(8,2),[b] numeric(8,[c] numeric(8,[d] numeric(8,[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,3.53),(20,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岁[超过] b的结果,并且缺少空值我想要为每次空碰到都包含null.

我发现用不同的值替换null然后替换所有那些常量不同的值方法对我的工作是不可行的.我想只包括在unpivot中.

解决方法

declare @pivot_task table
(
age int null,2)
);

insert into @pivot_task values (18,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],[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：

select a.age,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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!