我可以将表格取消,但结果中不包含空值.
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的带外替换：
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
