SQL Server 2019

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

create table t (id int, site_id varchar(10));
insert into t (id, site_id) values
  (1, '0070000036'),
  (1, '0090000028'),
  (1, '0100000002'),
  (2, '0100000002'),
  (1, '0100000004'),
  (1, '0100000009'),
  (2, '0100000009'),
  (3, '0100000009'),
  (1, '0100000029'),
  (2, '0100000029'),
  (3, '0100000029'),
  (1, '0100000030'),
  (2, '0100000030'),
  (1, '0100000032'),
  (2, '0100000032'),
  (3, '0100000032');
16 rows affected

select t.*,
  (dense_rank() over(order by site_id) - 1) % 7 + 1 as code_assignement
from t


id
site_id
code_assignement


1
0070000036
1

1
0090000028
2

1
0100000002
3

2
0100000002
3

1
0100000004
4

1
0100000009
5

2
0100000009
5

3
0100000009
5

1
0100000029
6

2
0100000029
6

3
0100000029
6

1
0100000030
7

2
0100000030
7

1
0100000032
1

2
0100000032
1

3
0100000032
1

id	site_id	code_assignement
1	0070000036	1
1	0090000028	2
1	0100000002	3
2	0100000002	3
1	0100000004	4
1	0100000009	5
2	0100000009	5
3	0100000009	5
1	0100000029	6
2	0100000029	6
3	0100000029	6
1	0100000030	7
2	0100000030	7
1	0100000032	1
2	0100000032	1
3	0100000032	1