isnull pivot server sql_SQL Server Pivot用最后一列替换null

Pivot table:

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

Result:

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

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;