How loop one table with parameter from another table

I have 2 tables below:
table grade is to set the grade to student with allocate qty, zero value means the rest record of students.

tblGrade	
Rank	Grade	Allocate Qty	Remark
1	A	3	
2	B+	5	
3	B	5	
4	C	5	
5	D	0	The rest

and tblStudentGrade with 2 coluomns StudentID and Score. I need to query this table with parameter from tblGrade to get below result.

tblStudentGrade		
StudentID	Score	Grade
S0001	100	A
S0002	99	A
S0014	99	A
S0008	98	B+
S0013	90	B+
S0007	78	B+
S0024	75	B+
S0022	68	B+
S0018	66	B
S0004	59	B
S0006	56	B
S0011	56	B
S0012	56	B
S0017	55	C
S0009	54	C
S0003	50	C
S0023	45	C
S0016	34	C
S0021	26	D
S0005	23	D
S0010	23	D
S0019	23	D
S0020	18	D
S0015	12	D

Your help is much appreciated.

You can use window function to get the result in a few step. Use rank() instead of row_number() so it can handle the tide.

Step 1. Use window function to calcuate start and end student index based on cumulative AllocateQty. On the last rank, we can use a trick to set end index of last rank to be arbitrary large number (let's say 10,000 is large enough for a reasonable classroom registration size)

Here is the step 1 query:

select
    rank, 
    Grade,
    AllocateQty,
    coalesce(sum(AllocateQty) over (order by rank ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) + 1 as start_student_index,
    (case when AllocateQty = 0 then 10000 else coalesce(sum(AllocateQty) over (order by rank ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) + AllocateQty end)  as end_student_index
from
tblGrade

Step 2: Use window functiona and rank() to calculate student_grade_rank and join with grade_index. Bound the join result by student_grade_rank between grade_start_index and grade_end_index.

Here is the full query:

with gradeStudentIndex AS (
select
   rank, 
   Grade,
   AllocateQty,
   coalesce(sum(AllocateQty) over (order by rank ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) + 1 as start_student_index,
   (case when AllocateQty = 0 then 10000 else coalesce(sum(AllocateQty) over (order by rank ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) + AllocateQty end)  as end_student_index
from
    tblGrade
),
studentGradeRank AS (
select
    studentID,
    score,
    rank() over (order by score desc) as student_grade_rank
from
    tblStudentGrade     
)
select
    sr.studentID,
    sr.score,
    si.grade
from 
    StudentGradeRank sr
join
    gradeStudentIndex si
on
    sr.student_grade_rank BETWEEN si.start_student_index AND si.end_student_index
order by sr.student_grade_rank

Something like below will work:

CREATE TABLE #TempTable (
    Rank int,
    Grade varchar(2),
    AllocateQty int,
    Remark varchar(50)
);

INSERT INTO #TempTable (Rank, Grade, AllocateQty, Remark)
VALUES (1, 'A', 3, ''),
       (2, 'B+', 5, ''),
       (3, 'B', 5, ''),
       (4, 'C', 5, ''),
       (5, 'D', 0, 'The rest');

WITH Grades ([rank], [grade]) AS
(
	SELECT ROW_NUMBER() OVER (ORDER BY Rank)
		  ,Grade
	FROM #TempTable T
	INNER JOIN
	(
		SELECT row_number() over(order by t1.number) as N
		FROM master..spt_values t1 
	) DS
		ON T.[AllocateQty] >= DS.n
)
SELECT S.*
	  ,ISNULL(G.[grade], 'D')
FROM
(
   SELECT *
	  ,ROW_NUMBER() OVER (ORDER BY Score, StudentID) AS [Rank]
	FROM tblStudentGrade 
) S
LEFT JOIN Grades G
	ON S.[Rank] = G.[Rank]

First, we need to duplicate the grades table using the AllocateQty column, and then generate ranks - marks pairs. Then, generate rank for each student and simply join the two data sources.

It's raw. You need to improve depending your real scenario. For example, if two or more students have the same score, we order by student ID, but maybe these students need to have one rank (use DENSE_RANK in such case for example).