Handle NULL value in UNPIVOT

Question

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;

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.

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.

'For 18 age [over]'? Does this contain particularly graphic or violent SQL? — Paul, Apr 23 '14 at 08:00
@Westie Are you saying in pivot_task table replace null value with other then unpivot but I can't modify the source table pivot_task. — Vishwanath Dalvi, Apr 23 '14 at 08:04
If your numeric belongs in the specified range (says non-negative) you can workaround this issue. +1 for well-formed question. — Hamlet Hakobyan, Apr 23 '14 at 08:07
Unfortunately, [`UNPIVOT`](http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx) is documented to eliminate `NULL`s - "null values in the input of `UNPIVOT` disappear in the output" - so there's no "unpivot only" solution to this problem. — Damien_The_Unbeliever, Apr 23 '14 at 08:36

score 4 · Accepted Answer · answered Apr 23 '14 at 08:42

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

yeah the 2nd query to eliminate the UNPIVOT entirely worked, but would you explain a bit how this query works? — Vishwanath Dalvi, Apr 23 '14 at 09:02
A `CROSS JOIN` pairs every row from the left table with every row from the right table. In this case, the left table is just the set of five rows containing `a` - `e`, and the right table is your original `pivot_task` table. So we end up with the age `18` row paired with each of `a` - `e`, the `19` row paired up with `a` - `e` ,... Then in the `CASE` expression, we just select the column that matches the letter that the `pivot_task` row is currently paired with. — Damien_The_Unbeliever, Apr 23 '14 at 09:05

t-clausen.dk · Answer 2 · 2014-04-23T09:04:44.063

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;

score 1 · Answer 3 · answered Apr 23 '14 at 10:30

1

USE ISNULL(columnname ,0) http://technet.microsoft.com/en-us/library/ms184325.aspx FOR ALL COLUMN BEFORE UNPIVOT IT.

like below.

Select Age, Data, Case When (Value = 0) Then NULL Else Value End Value
from (
select age, 
    ISNULL([a],0)[a], ISNULL([b],0)[b], ISNULL([c],0)[c], ISNULL([d],0)[d], ISNULL([e],0)[e]
From pivot_task) As pvttask
UnPivot ([Value] for [Data] In ([a], [b], [c], [d], [e])) a

answered Apr 23 '14 at 10:30

Ritesh Khatri

484
4
13

isn't this basically the same answer as the one posted by @VigneshKumar ? – t-clausen.dk Apr 23 '14 at 12:08

Handle NULL value in UNPIVOT

3 Answers3

Linked