SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable(
   Id                 INTEGER  NOT NULL 
  ,UnitNo          VARCHAR(80)
  ,TenantName      VARCHAR(50)
  ,NetRent         NUMERIC(6,2) NOT NULL
  ,SecurityDeposit NUMERIC(9,2)
);
INSERT INTO mytable
(Id,UnitNo,TenantName,NetRent,SecurityDeposit) VALUES 
(1,'#k1-30','Kkks Pte Ltd',12.4,115000.12),
(2,NULL,NULL,14.4,NULL),
(3,NULL,NULL,16.4,NULL),
(4,'#K1-40','crown Pte Ltd',22.60,215000.12),
(5,NULL,NULL,24.60,NULL),
(6,NULL,NULL,26.60,NULL),
(7,'#K14-01','Techno pv Ltd',64.60,124230.12),
(8,NULL,NULL,27.60,NULL);
8 rows affected

select
Id,
coalesce([UnitNo], max([UnitNo]) over (partition by [UnitNo1])) as [UnitNo1],
coalesce([TenantName], max([TenantName]) over (partition by [TenantName1])) as [TenantName1],
NetRent,  
coalesce([SecurityDeposit], max([SecurityDeposit]) over (partition by [SecurityDeposit1])) as [SecurityDeposit1]
  
from (
select t.*,
max(case when [UnitNo] is not null then Id end) over (order by Id) as [UnitNo1],
max(case when [TenantName] is not null then Id end) over (order by Id) as [TenantName1],
max(case when [NetRent] is not null then Id end) over (order by Id) as [NetRent1],
max(case when [SecurityDeposit] is not null then Id end) over (order by Id) as [SecurityDeposit1]
 
from mytable t
) t


Id
UnitNo1
TenantName1
NetRent
SecurityDeposit1


1
#k1-30
Kkks Pte Ltd
12.40
115000.12

2
#k1-30
Kkks Pte Ltd
14.40
115000.12

3
#k1-30
Kkks Pte Ltd
16.40
115000.12

4
#K1-40
crown Pte Ltd
22.60
215000.12

5
#K1-40
crown Pte Ltd
24.60
215000.12

6
#K1-40
crown Pte Ltd
26.60
215000.12

7
#K14-01
Techno pv Ltd
64.60
124230.12

8
#K14-01
Techno pv Ltd
27.60
124230.12


Warning: Null value is eliminated by an aggregate or other SET operation.



;with t as
(
select
Id,UnitNo,TenantName,NetRent,SecurityDeposit,
coalesce([UnitNo], max([UnitNo]) over (partition by [UnitNo1])) as [UnitNo1],
coalesce([TenantName], max([TenantName]) over (partition by [TenantName1])) as [TenantName1],
coalesce([SecurityDeposit], max([SecurityDeposit]) over (partition by [SecurityDeposit1])) as [SecurityDeposit1]
  
from (
select t.*,
max(case when [UnitNo] is not null then Id end) over (order by Id) as [UnitNo1],
max(case when [TenantName] is not null then Id end) over (order by Id) as [TenantName1],
max(case when [SecurityDeposit] is not null then Id end) over (order by Id) as [SecurityDeposit1]
from mytable t
) t
  
)

update t
set UnitNo=[UnitNo1],
    TenantName=[TenantName1],
    SecurityDeposit=SecurityDeposit1


select * from mytable


Id
UnitNo
TenantName
NetRent
SecurityDeposit


1
#k1-30
Kkks Pte Ltd
12.40
115000.12

2
#k1-30
Kkks Pte Ltd
14.40
115000.12

3
#k1-30
Kkks Pte Ltd
16.40
115000.12

4
#K1-40
crown Pte Ltd
22.60
215000.12

5
#K1-40
crown Pte Ltd
24.60
215000.12

6
#K1-40
crown Pte Ltd
26.60
215000.12

7
#K14-01
Techno pv Ltd
64.60
124230.12

8
#K14-01
Techno pv Ltd
27.60
124230.12


Warning: Null value is eliminated by an aggregate or other SET operation.

Id	UnitNo1	TenantName1	NetRent	SecurityDeposit1
1	#k1-30	Kkks Pte Ltd	12.40	115000.12
2	#k1-30	Kkks Pte Ltd	14.40	115000.12
3	#k1-30	Kkks Pte Ltd	16.40	115000.12
4	#K1-40	crown Pte Ltd	22.60	215000.12
5	#K1-40	crown Pte Ltd	24.60	215000.12
6	#K1-40	crown Pte Ltd	26.60	215000.12
7	#K14-01	Techno pv Ltd	64.60	124230.12
8	#K14-01	Techno pv Ltd	27.60	124230.12