SQL Server 2017

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

CREATE TABLE dbo.Semester
(
  Id               int NOT NULL,
  Semester varchar(10) NOT NULL
);
CREATE TABLE dbo.Student
(
  Id                  int NOT NULL,
  StudentName varchar(10) NOT NULL
);
CREATE TABLE dbo.Enrollment
(
  Id         int NOT NULL,
  StudentID  int NOT NULL,
  SemesterID int NOT NULL
);
INSERT INTO
  dbo.Semester (Id, Semester)
VALUES
  (1, 'First'),
  (2, 'Second'),
  (3, 'Third')
;
3 rows affected

INSERT INTO
  dbo.Student (Id, StudentName)
VALUES
  (1, 'A'),
  (2, 'B'),
  (3, 'C'),
  (4, 'D'),
  (5, 'E'),
  (6, 'F'),
  (7, 'G'),
  (8, 'H'),
  (9, 'I'),
  (10, 'J'),
  (11, 'K'),
  (12, 'L')
;
12 rows affected

INSERT INTO
  dbo.Enrollment (Id, StudentID, SemesterID)
VALUES
  (1, 1, 1),
  (2, 2, 2),
  (3, 3, 2),
  (4, 4, 1),
  (5, 5, 2),
  (6, 6, 2),
  (7, 7, 1),
  (8, 8, 2),
  (9, 9, 1),
  (10, 10, 1),
  (11, 11, 2),
  (12, 12, 1)
;
12 rows affected

DECLARE @NumberOfStudents int = 3;
DECLARE @SemesterList varchar(1000) = 'First,Second';

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / @NumberOfStudents
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student  AS st ON rd.StudentID  = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
WHERE
  sm.Semester IN (SELECT value FROM STRING_SPLIT(@SemesterList, ','))
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;


Id
StudentName
Semester


1
A
First

4
D
First

7
G
First

2
B
Second

3
C
Second

5
E
Second

9
I
First

10
J
First

12
L
First

6
F
Second

8
H
Second

11
K
Second

Id	StudentName	Semester
1	A	First
4	D	First
7	G	First
2	B	Second
3	C	Second
5	E	Second
9	I	First
10	J	First
12	L	First
6	F	Second
8	H	Second
11	K	Second