oracle unpivot 空值,sql – 处理UNPIVOT中的NULL值

这很难看,但不依赖于必须找到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：

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