SQL Server 2022

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

CREATE TABLE table1 (
    ID INT,
    GRP INT,
    N_VO INT,
    DAT DATE
);


INSERT INTO table1 (ID, GRP, N_VO, DAT)
VALUES
    (1, 1, 1, '2022-05-10'),
    (1, 1, 2, '2022-07-12'),  
    (1, 3, 3, '2022-10-01'),
  
    (2, 1, 1, '2022-07-12'),
    (2, 1, 2, '2022-07-18'),
    (2, 3, 3, '2022-10-05'),  
  
    (3, 1, 1, '2022-02-18'),
    (3, 1, 2, '2022-04-19'),
    (3, 1, 3, '2022-06-22'),
  
    (4, 1, 1, '2021-02-04'),
    (4, 1, 2, '2021-03-14'),
    (4, 1, 3, '2021-11-08'),
    (4, 1, 4, '2021-12-04'),

    (5, 1, 1, '2022-08-17'),
    (5, 3, 2, '2022-09-01')


  

SELECT *
FROM table1
ORDER BY N_VO asc


ID
GRP
N_VO
DAT


1
1
1
2022-05-10

2
1
1
2022-07-12

4
1
1
2021-02-04

3
1
1
2022-02-18

5
1
1
2022-08-17

5
3
2
2022-09-01

3
1
2
2022-04-19

4
1
2
2021-03-14

2
1
2
2022-07-18

1
1
2
2022-07-12

1
3
3
2022-10-01

2
3
3
2022-10-05

4
1
3
2021-11-08

3
1
3
2022-06-22

4
1
4
2021-12-04


WITH RankedTable AS (
    SELECT
        ID,
        GRP,
        N_VO,
        DAT,
        ROW_NUMBER() OVER (PARTITION BY GRP, N_VO ORDER BY DAT) AS RN
    FROM
        table1
    WHERE
        GRP = 3
)

SELECT
    ID,
    GRP,
    N_VO,
    DAT,
    CASE
        WHEN COUNT(*) OVER (PARTITION BY GRP, N_VO) > 1
            THEN CONCAT(N_VO, '_', RN)
        ELSE
            CAST(N_VO AS NVARCHAR(MAX))
    END AS GRP_N_VO
FROM RankedTable;


ID
GRP
N_VO
DAT
GRP_N_VO


5
3
2
2022-09-01
2

1
3
3
2022-10-01
3_1

2
3
3
2022-10-05
3_2


WITH RankedTable AS (
    SELECT ID, GRP, N_VO, DAT,
        ROW_NUMBER() OVER (PARTITION BY GRP, N_VO ORDER BY DAT) AS RN
    FROM table1
    WHERE GRP = 3
),
grp_3_list as (
  SELECT ID, GRP, N_VO, DAT,
    CASE
        WHEN COUNT(*) OVER (PARTITION BY GRP, N_VO) > 1
            THEN CONCAT(N_VO, '_', RN)
        ELSE
            CAST(N_VO AS NVARCHAR(MAX))
    END AS GRP_N_VO
  FROM RankedTable
)
SELECT *
FROM grp_3_list
UNION ALL
SELECT t.*, CAST(t.N_VO AS NVARCHAR(MAX)) 
FROM table1 t
where t.N_VO NOT IN (SELECT N_VO FROM grp_3_list)
UNION ALL
SELECT t.ID, t.GRP, g.N_VO, t.DAT, GRP_N_VO
FROM table1 as t
INNER JOIN grp_3_list g on t.N_VO = g.N_VO and t.GRP <> g.GRP
ORDER BY N_VO, GRP_N_VO, DAT desc


ID
GRP
N_VO
DAT
GRP_N_VO


5
1
1
2022-08-17
1

2
1
1
2022-07-12
1

1
1
1
2022-05-10
1

3
1
1
2022-02-18
1

4
1
1
2021-02-04
1

5
3
2
2022-09-01
2

2
1
2
2022-07-18
2

1
1
2
2022-07-12
2

3
1
2
2022-04-19
2

4
1
2
2021-03-14
2

1
3
3
2022-10-01
3_1

3
1
3
2022-06-22
3_1

4
1
3
2021-11-08
3_1

2
3
3
2022-10-05
3_2

3
1
3
2022-06-22
3_2

4
1
3
2021-11-08
3_2

4
1
4
2021-12-04
4

ID	GRP	N_VO	DAT
1	1	1	2022-05-10
2	1	1	2022-07-12
4	1	1	2021-02-04
3	1	1	2022-02-18
5	1	1	2022-08-17
5	3	2	2022-09-01
3	1	2	2022-04-19
4	1	2	2021-03-14
2	1	2	2022-07-18
1	1	2	2022-07-12
1	3	3	2022-10-01
2	3	3	2022-10-05
4	1	3	2021-11-08
3	1	3	2022-06-22
4	1	4	2021-12-04