How to fill empty cells with previous non empty value or with next empty value if previous not empty value does not exists

All Replies

• Wednesday, February 27, 2013 2:23 PM

  Moderator

   

   
  

  0

  If you are using SS 2012 then check the offset functions (LEAD / LAG) in BOL, otherwise you could use a self join approach.

  Edit: LAG and LEAD will not work well if we can have multiple consecutive rows where [value] is the NULL mark.

  ---

  AMB
  

  Some guidelines for posting questions...

  
  
  

  • Edited by HunchbackMVP, Moderator Wednesday, February 27, 2013 2:26 PM
  • Edited by HunchbackMVP, Moderator Wednesday, February 27, 2013 4:29 PM
  •  
  • Reply
  • Quote

   
• Wednesday, February 27, 2013 3:32 PM

   

   
  

  0

  Try like this,

  declare @Table table (name varchar(10),value int,number varchar(5))
  insert into @Table
  select 'alice', 12,'' union        
  select 'christa',13,'1' union
  select 'hally',14,'' union
  select 'jack', 18,'2' union
  select 'raj',  20,'' union
  select 'jane', 22,'3' union
  select 'jodie',25,'' 
  
  ;with t as(
  select ROW_NUMBER() over(order by value) row,* from @Table
  )
  
  select a.name,b.value,
  case when a.number= '' then b.number
  else a.number
  End Number 
  from t a , t b
  where a.row+1=b.row

  
  

  ---

  Regards, Dineshkumar
  Please "Mark as Answer" if my post answers your question or "Vote as Helpful" if it helps you

  • Reply
  • Quote

   
• Wednesday, February 27, 2013 3:44 PM

  Moderator

   

   
  

  0

  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 
  

  
  
  • Reply
  • Quote

   
• Wednesday, February 27, 2013 4:28 PM

  Moderator

   

   
  

  0

  Jingyang Li,

  Nice solution!

  If the logic is based on the order of [person_id], then you can avoid ROW_NUMBER.

  WITH R AS (
  SELECT
  	[@T].person_id, [@T].[value], COALESCE(A.val, B.val) AS val
  FROM
  	@T
  	OUTER APPLY
  	(
  	SELECT TOP (1) [value]
  	FROM @T AS T
  	WHERE T.person_id < [@T].person_id AND T.value IS NOT NULL
  	ORDER BY T.person_id DESC
  	) AS A(val)
  	OUTER APPLY
  	(
  	SELECT TOP (1) [value]
  	FROM @T AS T
  	WHERE T.person_id > [@T].person_id AND T.value IS NOT NULL
  	ORDER BY T.person_id
  	) AS B(val)
  WHERE
  	[@T].value IS NULL
  )
  UPDATE R
  SET [value] = val;

  
  
  

  ---

  AMB
  

  Some guidelines for posting questions...

  
  
  

  • Edited by HunchbackMVP, Moderator Wednesday, February 27, 2013 4:32 PM
  • Edited by HunchbackMVP, Moderator Wednesday, February 27, 2013 4:33 PM
  • Marked As Answer by Iric WenModerator Thursday, March 07, 2013 8:56 AM
  •  
  • Reply
  • Quote