SQL Server Pivot用最后一列替换null [英] sql server pivot replace null with last column
本文介绍了SQL Server Pivot用最后一列替换null的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
数据透视表:
id Jan Feb March April May June
=========================================
1 0.12 0.36 0.72 null null null
2 null null 0.11 0.12 0.36 null
结果:
id Jan Feb March April May June
=========================================
1 0.12 0.36 0.72 0.72 0.72 0.72
2 0.00 0.00 0.11 0.12 0.36 0.36
如果在行/列中没有值的单元格的开头,则应将null替换为0,然后如果存在null,则该行中的前一个单元格应填充同一行中的下一个单元格.
At the begin of the cell in row/column if there is no value null should be replaced by 0 and then previous cell in the row should fill the next cell in the same row if there is null.
推荐答案
-- sample table for discussion
create table tbl (
id int,
month varchar(9),
value float);
insert tbl values
(1,'Jan',0.12),
(1,'Feb',0.36),
(1,'Mar',0.72),
(2,'Mar',0.11),
(2,'Apr',0.12),
(2,'May',0.36);
-- beginning of script
declare @tbl table (
id int,
number int,
month varchar(9),
value float);
insert @tbl
select id.id, Months.Number, Months.Name, t.value
from (values(1,'Jan'),
(2,'Feb'),
(3,'Mar'),
(4,'Apr'),
(5,'May'),
(6,'Jun')) Months(Number,Name)
cross join (select distinct id from tbl) id
left join tbl t on t.month = Months.name and t.id=id.id;
-- fully populate the table with all id/month combinations filled in
;with cte as (
select id,Number,month,isnull(Value,0.0)value
from @tbl
where Number=1
union all
select cte.id,t.Number,t.month,isnull(t.value,cte.Value)
from cte
join @tbl t on t.id=cte.id and t.number=cte.number+1
)
-- this is what the original pivot would have looked like
select id, Jan,Feb,Mar,Apr,May,Jun
from (select id,month,value from cte) p
pivot (max(value) for month in (Jan,Feb,Mar,Apr,May,Jun)) v;
这篇关于SQL Server Pivot用最后一列替换null的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文