Repeat value between two values in a column

  • A+
Category:Languages

I'm trying to repeat the first specific non-empty value in a column till the next specific non-empty value in the same column. How do I do that?

The data looks like this:

ID | Msg   ---+-----  1 |       2 |    3 |    4 |    5 | Beg    6 | End    7 |    8 | Beg    9 |   10 |    11 |   12 | End 

It should be like this:

ID | Msg   ---+-----  1 |       2 |    3 |    4 |    5 | Beg    6 | End    7 |    8 | Beg      9 | Beg    10 | Beg     11 | Beg   12 | End 

I looked into LAG() and LEAD() but I keep thinking that I would have to use a CURSOR for it. I just know about those but have not yet used them in such a case.

 


Just another option using a Flag and a sum() over

Example

Select ID       ,Msg = case when sum( case when [Msg]='Beg' then  1 when [Msg]='End' then -1  else 0 end ) over (order by ID) = 1 and Msg='' then 'Beg' else Msg end  From  YourTable 

Returns

ID  Msg 1    2    3    4    5   Beg 6   End 7    8   Beg 9   Beg 10  Beg 11  Beg 12  End 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: