recursive cte for below pattern

Question

input:

col1 col2
1    val
2    Null
3    Null
4    val1
5    Null

output:

col1 col2
1    val
2    val
3    val
4    val1
5    val1

Concept:wherever it is null in column it should search previous row to get the value instead of null,if previous row is null then it should search previous of that row.likewise there shouldnt be any null it will be replaced by value of most recent previous value.SQl server 2012

Answer 1

You don't need a recursive CTE for this. A simple method is:

select t.col1, tt.col2
from t outer apply
     (select top 1 col2
      from t t2
      where t2.col1 <= t1.col1 and t2.col2 is not null
      order by t2.col1 desc
     ) tt;

Here is a SQL Fiddle (thanks to John Woo).

The actual functionality you want is available through he ANSI standard lag(col2 ignore nulls) over (order by col1). However, that is not supported by SQL Server.

The most efficient method uses window functions. I would recommend:

select col1, max(col2) over (partition by grp) as col2
from (select t.*,
             count(col2) over (order by col1) as grp
      from t
     ) t;

I'm pretty sure this method is much more scalable and faster than a recursive CTE.

Answer 2

Here is solution using recursive cte

with cte as (
    select
        *, rn = row_number() over (order by col1)
    from
        (values
            (1, 'val')
            ,(2 , Null)
            ,(3, Null)
            ,(4, 'val1')
            ,(5, Null)
        ) t(col1, col2)
)
, rcte as (
    select
        col1, col2, rn
    from
        cte
    union all
    select
        a.col1, b.col2, b.rn
    from
        rcte a
        join cte b on a.rn - 1 = b.rn
    where
        a.col2 is null
)
select col1, col2 from rcte
where col2 is not null
order by col1