copy data from Previous non null rows and if first row is null copy next non null value

Question

I need to copy data from rows with non-null rows and if first row is null copy the next non-null value.

it is not a big table. There is no ID column and date column.

The datatype of this column is varchar.

I am using SQL Server 2012

This is how the data is in the table:

Masked
5574-12XX-XXXX-1338
6674-13XX-XXXX-0855
7784-14XX-XXXX-5237
Null
Null
Null
9984-11XX-XXXX-6196
3384-11XX-XXXX-0615
Null
Null

This is expected result.

Masked
5574-12XX-XXXX-1338
6674-13XX-XXXX-0855
7784-14XX-XXXX-5237
7784-14XX-XXXX-5237
7784-14XX-XXXX-5237
7784-14XX-XXXX-5237
9984-11XX-XXXX-6196
3384-11XX-XXXX-0615
3384-11XX-XXXX-0615
3384-11XX-XXXX-0615

online solutions work with simple data. but I am struggling with this data. I am very desperate for the solution. Thanks in advance.

Answer 1

For that you need an ORDER and another column that helps get the last number

the cte #help1 get you the order with Row# needed for the following tables ChangeIndicator is need t make a group , s that you can get the corect masked value

In #help2 the groupy are established, so that you can get the first vaule of the group, which has the value, you want fr that you need also the order built in #help1.

CREATE TABLE table1
    ([Masked] varchar(19))
;
    
INSERT INTO table1
    ([Masked])
VALUES
    ('5574-12XX-XXXX-1338'),
    ('6674-13XX-XXXX-0855'),
    ('7784-14XX-XXXX-5237'),
    (NULL),
    (NULL),
    (NULL),
    ('9984-11XX-XXXX-6196'),
    ('3384-11XX-XXXX-0615'),
    (NULL),
    (NULL)
;
GO

WITH #help1 as (
select *
,ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS Row#
, case when [Masked] is null then 0 else 1 end as ChangeIndicator
from [dbo].[table1]
)
, #help2 as (
select *, Sum(ChangeIndicator) over (ORDER BY Row#) RowGroup from #help1
)
select 
case when [Masked] is not null then [Masked]
else first_value([Masked]) over (partition by RowGroup ORDER BY Row#)
end UnitsBalanceFillDown
from #help2

GO

| UnitsBalanceFillDown |
| :------------------- |
| 5574-12XX-XXXX-1338  |
| 6674-13XX-XXXX-0855  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 7784-14XX-XXXX-5237  |
| 9984-11XX-XXXX-6196  |
| 3384-11XX-XXXX-0615  |
| 3384-11XX-XXXX-0615  |
| 3384-11XX-XXXX-0615  |

db<>fiddle here