Fill Null For Another Column T-sql



  
  Create table TableA (id int identity(1,1), ColumnA varchar(10))
insert into TableA values('111'),('AAA'),('BBB'),('CCC'),('222'),('XXX'),('AAA'),('DDD')


;with mycte as (
select ColumnA
,Case WHEN ISNUMERIC(ColumnA)=1  Then ColumnA 
 Else null 
END ColumnB  ,Row_number() Over(Order by id) rn   
FROM TableA )

Select ColumnA,d.ColumnB from mycte m
Cross apply (select top 1 ColumnB from mycte m2 
WHERE m2.rn<=m.rn and ColumnB is not null   
Order by rn DESC) d(ColumnB) 
WHERE  ISNUMERIC(ColumnA)=0


Drop table TableA


 

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e947b82b-32e8-43c1-bbe2-02b6e75d01bf/complex-query?forum=transactsql

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