SQL Server 2017

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

CREATE TABLE t
    (
        [ID]    INT
      , [Alias] NVARCHAR(50)
      , [Key]   INT
      , [Val]   NVARCHAR(10)
      , [RN]    INT
    )
INSERT INTO t
    (
        [ID]
      , [Alias]
      , [Key]
      , [Val]
      , [RN]
    )
VALUES
    ( 1, N'A1', 1, N'One', 1 )
  , ( 1, N'A1', 2, N'Two', 1 )
  , ( 1, N'A1', 3, N'', 1 )
  , ( 1, N'A2', 1, N'One', 2 )
  , ( 1, N'A2', 2, N'', 2 )
  , ( 1, N'A2', 3, N'Three', 2 )
  , ( 1, N'A3', 1, N'One', 3 )
  , ( 1, N'A3', 2, N'Two', 3 )
  , ( 1, N'A3', 4, N'Four_New', 3 )
  , ( 1, N'A4', 4, N'Four', 4 )
  , ( 1, N'A4', 5, N'Five', 4 )

  , ( 2, N'B1', 1, N'', 1 )
  , ( 2, N'B1', 2, N'', 1 )
  , ( 2, N'B1', 3, N'', 1 )
  , ( 2, N'B2', 1, N'One', 2 )
  , ( 2, N'B2', 2, N'', 2 )
  , ( 2, N'B2', 3, N'', 2 )
  , ( 2, N'B3', 2, N'Two', 3 )
  , ( 2, N'B3', 4, N'Four', 3 )
  , ( 2, N'B4', 3, N'Three', 4 )
  , ( 2, N'B4', 6, N'Six', 4 )
21 rows affected

with base_rows
  as (select *
            ,rank() over(partition by id,[key] order by rn asc) as rnk
            ,cast(max(substring(alias,2,len(alias))) over(partition by id)+1 as nvarchar(100)) as max_index
            ,max(substring(alias,1,1)) over(partition by id) as alias_char
        from t
      )
 select id
        ,concat(alias_char,max_index)
        ,[key]
        ,[val]
        ,[rn]
   from base_rows
 where rnk=1


id
(No column name)
key
val
rn


1
A5
1
One
1

1
A5
2
Two
1

1
A5
3

1

1
A5
4
Four_New
3

1
A5
5
Five
4

2
B5
1

1

2
B5
2

1

2
B5
3

1

2
B5
4
Four
3

2
B5
6
Six
4


with base_rows
  as (select *
            ,rank() over(partition by id,[key] order by rn asc) as rnk
            ,cast(max(substring(alias,2,len(alias))) over(partition by id)+1 as nvarchar(100)) as max_index
            ,max(substring(alias,1,1)) over(partition by id) as alias_char
        from t
      where [val] <> ''  
      )
 select id
        ,concat(alias_char,max_index)
        ,[key]
        ,[val]
        ,[rn]
   from base_rows
 where rnk=1


id
(No column name)
key
val
rn


1
A5
1
One
1

1
A5
2
Two
1

1
A5
3
Three
2

1
A5
4
Four_New
3

1
A5
5
Five
4

2
B5
1
One
2

2
B5
2
Two
3

2
B5
3
Three
4

2
B5
4
Four
3

2
B5
6
Six
4

id	(No column name)	key	val	rn
1	A5	1	One	1
1	A5	2	Two	1
1	A5	3		1
1	A5	4	Four_New	3
1	A5	5	Five	4
2	B5	1		1
2	B5	2		1
2	B5	3		1
2	B5	4	Four	3
2	B5	6	Six	4

id	(No column name)	key	val	rn
1	A5	1	One	1
1	A5	2	Two	1
1	A5	3	Three	2
1	A5	4	Four_New	3
1	A5	5	Five	4
2	B5	1	One	2
2	B5	2	Two	3
2	B5	3	Three	4
2	B5	4	Four	3
2	B5	6	Six	4