SQL Server 2019

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

CREATE TABLE t
    (STUDENT int, TIME int, SCORE int)
;
    
INSERT INTO t
    (STUDENT, TIME, SCORE)
VALUES
    (1, 1, 4),
    (1, 2, 3),
    (1, 3, 4),
    (2, 1, 2),
    (2, 2, 2),
    (2, 3, 8),
    (3, 3, 10),
    (3, 4, 10),
    (4, 1, 1),
    (4, 2, 3),
    (4, 3, 2),
    (4, 4, 4),
    (4, 5, 4),
    (4, 6, 5)
;
14 rows affected

select * from t


STUDENT
TIME
SCORE


1
1
4

1
2
3

1
3
4

2
1
2

2
2
2

2
3
8

3
3
10

3
4
10

4
1
1

4
2
3

4
3
2

4
4
4

4
5
4

4
6
5


select   student
        ,sum(score) as total_score
        ,concat((time+1)/2*2-1, '-', (time+1)/2*2) as semester
from     t
group by student, (time+1)/2
order by student


student
total_score
semester


1
7
1-2

1
4
3-4

2
4
1-2

2
8
3-4

3
20
3-4

4
4
1-2

4
6
3-4

4
9
5-6