Sql-server – Show table records based on user condition in SQL Server

One way to approach this is to start with the Enrollment table. Using the ROW_NUMBER ranking function, you can assign row numbers in groups of SemesterID:

SELECT
  *,
  RN = ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC)
FROM
  dbo.Enrollment
;

For your example you will get output like this:

Id  StudentID  SemesterID  RN
--  ---------  ----------  --
1   1          1           1
2   2          2           1
3   3          2           2
4   4          1           2
5   5          2           3
6   6          2           4
7   7          1           3
8   8          2           5
9   9          1           4
10  10         1           5
11  11         2           6
12  12         1           6

Now subtract one from RN and then divide the result by the required number of students. If the number of students is 3, then you will find that the aforementioned calculation turns a sequence of

1, 2, 3, 4, 5, 6, ...

into

0, 0, 0, 1, 1, 1, ...

That is to say, you will get a sequence consisting of groups of repeating numbers, three per group (because we divided by three). You can use that result as the primary sorting criterion.

Choose SemesterID as your second criterion and Id or StudentID as the third, so now the query will look like this:

SELECT
  *,
  GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
FROM
  dbo.Enrollment
ORDER BY
  GroupID ASC,
  SemesterID ASC,
  Id ASC
;

And this will be its output based on the sample provided:

Id  StudentID  SemesterID  GroupID
--  ---------  ----------  -------
1   1          1           0
4   4          1           0
7   7          1           0
2   2          2           0
3   3          2           0
5   5          2           0
9   9          1           1
10  10         1           1
12  12         1           1
6   6          2           1
8   8          2           1
11  11         2           1

You can see that the order of rows already matches the expected output, you just need to replace StudentID and SemesterID with their descriptions and get rid of the GroupID. Declare the above query as either a derived table or a CTE and then join tables Student and Semester to it. For my answer I chose the CTE option, and this is what I got:

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
    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
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

Output:

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

As you can see, the query we have so far works perfectly for the provided example: the results now match your expected output precisely. But the query is not finished yet: the filter on semester is missing.

Assuming that semesters will be provided as a comma-separated list of names/descriptions, what you can do is turn that list into a row set, with one name/description per row. You can then match that row set against the Semester table to include just the selected semesters. For the purpose of this answer I am assuming that you are using SQL Server 2016 or later version, so that the built-in SPLIT_STRING function can be used to help implement the semester filter:

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
    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('First,Second', ','))
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

In an earlier version of SQL Server you will have to provide your own method of turning a string list into a row set of substrings. Thankfully, solutions abound on the Internet, including the many questions and answers dedicated to this problem both here and on Stack Overflow. Aaron Bertrand discusses various methods in his blog article, Split strings the right way – or the next best way, which can be a good starting point.

As for our query, there remains one last step to take: parametrisation. Both the number of students and the list of selected semesters were hard-coded in the above queries. The final solution will need to use parameter references instead of the hard-coded values, something like this:

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
;

A live demo of this solution is available at db<>fiddle.uk.

_{Disclosure: this answer borrows partly from the now deleted answer by Josh Darnell.}