Handle NULL value in UNPIVOT

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

Result:

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

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

Try this, it will replace all the null values with 10000000 before unpivot that is not an acceptable number in numeric(8,2), so the value will not exist already. Then the value will be replaced by null after unpivot:

;WITH x as
(
select 
age,
coalesce(cast(a as numeric(9,2)), 10000000) a,
coalesce(cast(b as numeric(9,2)), 10000000) b,
coalesce(cast(c as numeric(9,2)), 10000000) c,
coalesce(cast(d as numeric(9,2)), 10000000) d,
coalesce(cast(e as numeric(9,2)), 10000000) e
from pivot_task
)
select age, [over], nullif([av], 10000000) av
from x
unpivot
(
 [av]
 for [over] in ([a], [b], [c], [d], [e])
) a;