Repeat value between two values in a column

Question

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.

LEAD would work fine. If the val is Beg, LEAD 1 value ahead and if that value is blank, fill it in with Beg else use the value that is there. — dfundako, Jan 14 '19 at 20:50
Deeper reading: https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/ — Andy, Jan 14 '19 at 21:18

John Cappelletti · Accepted Answer · 2019-01-14T22:09:13.867

6

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

edited Jan 14 '19 at 22:09

answered Jan 14 '19 at 22:04

John Cappelletti

79,615
7
44
66

1

Simplest answer here. – KumarHarsh Jan 15 '19 at 09:36
Thanks! Works for me. – SQLserving Jan 15 '19 at 13:52
@SQLserving Happy to help – John Cappelletti Jan 15 '19 at 13:53

score 4 · Answer 2 · answered Jan 14 '19 at 21:16

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

I would any day prefer your solution over mine. Thanks and keep up the good work! — Aura, Jan 14 '19 at 21:27

score 3 · Answer 3 · answered Jan 14 '19 at 21:19

Data

DECLARE @id AS TABLE (
    ID INT
,   MSG VARCHAR(3)
)

INSERT INTO @ID (ID, MSG)
SELECT 1, ''
UNION
SELECT 2, ''
UNION
SELECT 3, ''
UNION
SELECT 4, ''
UNION
SELECT 5, 'Beg'
UNION
SELECT 6, 'End'
UNION
SELECT 7, ''
UNION
SELECT 8, 'Beg'
UNION
SELECT 9, ''
UNION
SELECT 10, ''
UNION
SELECT 11, ''
UNION
SELECT 12, 'End'

Query

SELECT 
    final.id 
,   CASE 
        WHEN msg = '' AND C.begCount>c.EndCount THEN 'Beg' 
        ELSE final.MSG
    END Msg
FROM @id final
INNER JOIN 
(
SELECT ID
,   (SELECT COUNT(*) FROM @ID B WHERE B.ID < MAIN.ID AND MSG ='BEG') begCount
,   (SELECT COUNT(*) FROM @ID B WHERE B.ID < MAIN.ID AND MSG ='END') EndCount 
FROM @id MAIN
) C
ON C.ID = final.ID

score 3 · Answer 4 · answered Jan 14 '19 at 22:26

Here is sql without self joins - using only window functions

select
     dat.id, 
     isnull(nullif(max(dat.msg) over (partition by dat.gr), 'End'), dat.msg) as msg
from (
    select
         dat.id,
         dat.msg,
         dat.wind + sum(dat.is_end) over (order by dat.id) as gr
    from (
        select
             t.id, 
             t.msg,
             sum(iif(t.msg = 'Beg' ,1,0)) over (order by t.id) as wind,
             iif (t.msg = 'End', 1, 0) as is_end
        from t
    ) dat
) dat

score 1 · Answer 5 · answered Jan 14 '19 at 21:20

You can try the following code, it will work for the case that you have asked but not sure if it will work for all the other cases since the case statement is specific to above positioning described:

WITH cte AS(
SELECT *, LEAD(Msg, 1, 0) OVER (ORDER BY ID) AS leadval, LAG(Msg, 1, 0) OVER (ORDER BY ID) AS lagval
FROM msg),
cte2 AS(
SELECT cte.ID,
       cte.Msg,
       cte.leadval,
       cte.lagval,
       CASE WHEN cte.Msg = 'Beg' THEN 'Beg'
       WHEN cte.Msg = '' AND cte.leadval = '' AND cte.lagval = 'Beg' THEN 'Beg'
       WHEN cte.Msg = '' AND cte.leadval = 'END' THEN 'Beg'
       ELSE cte.Msg end AS Msg2
FROM cte), cte3 AS(
SELECT *, LEAD(cte2.Msg2, 1, 0) OVER (ORDER BY cte2.ID) AS 'LeadVal2'
FROM cte2)
SELECT ID, CASE WHEN cte3.Msg2 = '' AND cte3.LeadVal2 = 'Beg' AND cte3.leadval <> 'Beg' THEN 'Beg' ELSE cte3.Msg2 END AS msg
FROM cte3

Output:

Repeat value between two values in a column

5 Answers5