Geek Answers Handbook

Use NULL in UNPIVOT

I can disable the 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 of http://sqlfiddle.com/#!6/2ab59/1 for 18 years [more] b and its zero value is missing. I want to also include null for every null meeting.

I found replacing null with another value, and then replacing all these methods with a constant different value is not practical for my work. I want to include only in univot.

+6
source share
3 answers

This is ugly, but does not rely on the need 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 go this route, you can completely eliminate UNPIVOT :

 select a.age, pmu.[over], CASE pmu.[over] WHEN 'a' THEN aa WHEN 'b' THEN ab WHEN 'c' THEN ac WHEN 'd' THEN ad WHEN 'e' THEN ae 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
+4
source

Try this, it will replace all zero values ​​with 10000000 before the key is opened, which is not a valid number in the numeric (8.2), so the value will no longer exist. Then the value will be replaced with zero after univot:

 ;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;
+3
source

USE ISNULL (column name, 0) http://technet.microsoft.com/en-us/library/ms184325.aspx FOR ALL COLUMNS BEFORE CONTINUOUS.

as shown 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
+1
source

More articles:


All Articles