How to Fill Column Null Values from Previous Non-null Value or Next if No Previous Exists




create table test2 (name varchar(50), person_id int,value int)
insert into test2 values ('alice',    12 , null)  ,    
('christa', 13,  1),
('hally',14,   null),
('jack',  18,   2),
('raj', 20,   null),
('jane',22,    3),
('jodie',  25, null)

select * from test2


;with mycte as
(
SELECT name,person_id,value, row_number()over(order by person_id ) rn
FROM test2 A)

SELECT m.name,m.person_id, ISNULL(d.value,e.value) AS value
FROM   mycte m
       OUTER APPLY (SELECT TOP 1 *  FROM   mycte
                    WHERE  rn <= m.rn AND value IS NOT NULL
                    ORDER  BY rn DESC ) d
       OUTER APPLY (SELECT TOP 1 *  FROM   mycte
                    WHERE  Value IS NOT NULL
                    ORDER  BY rn) e
ORDER  BY m.person_id 


drop table test2

--http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d7af7f8f-8acf-4a03-b3d1-297218aa5d60

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s