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
Since you are using MSSQL, you can write a CTE to get the result you are looking for.
Try this CTE:
declare @tab table
(
id int,
msg char(3)
)
insert into @tab
values
(1, ''),
(2, ''),
(3, ''),
(4, ''),
(5, 'Beg'),
(6, 'End'),
(7, ''),
(8, 'Beg'),
(9, ''),
(10, ''),
(11, ''),
(12, 'End')
;with cte as
(
select top 1 tab.id, tab.msg
from @tab tab
order by tab.id
union all
select tab.id, case when tab.msg = '' and cte.msg = 'beg' then cte.msg else tab.msg end
from @tab tab
inner join cte cte on cte.id + 1 = tab.id
)
select *
from cte
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With