DECLARE @TeacherGrades table (TCHLASID varchar(50), Grades varchar(50))

DECLARE @TCHLASID varchar(50)
DECLARE @LastTCHLASID varchar(50) 
DECLARE @Grade varchar(2)
DECLARE @GradeString varchar(50)
DECLARE @RowRun int

set @RowRun = 1

Create Table #TchGrades (TchLASID varchar(50), vchGrade varchar(50))

--Replace the table inserts below with a table join from your SIS that links class staff to class to classroster as student rosters carry the grade levels
insert #TchGrades select '111','K'
insert #TchGrades select '111','1'
insert #TchGrades select '111','2'
insert #TchGrades select '112','1'
insert #TchGrades select '112','2'



DECLARE Teacher_GradeCursor CURSOR FOR 
SELECT TchLASID, 
              vchGrade
FROM @TchGrades
order by TchLASID, 
CASE WHEN vchgrade LIKE '[0-9]%' THEN 'ZZZ' + vchgrade 
     When vchgrade like 'K' then 'ZZ' + vchGrade
     ELSE vchgrade END ASC

OPEN Teacher_GradeCursor 
   FETCH NEXT FROM Teacher_GradeCursor INTO @TCHLASID, @Grade 

  WHILE @@FETCH_STATUS = 0
   Begin

       if @LastTCHLASID = @TCHLASID
       begin
              set @GradeString  = @GradeString + @Grade + '|'

       end

       else
       begin
        If @RowRun > 1
        begin
          Insert @TeacherGrades select @LastTCHLASID, Substring(@GradeString, 1, len(@GradeString) - 1) 
              end
        
        set @LastTCHLASID = @TCHLASID
              set @GradeString  = @Grade + '|'
        set @RowRun = @RowRun + 1 
       end
       
     FETCH NEXT FROM Teacher_GradeCursor INTO @TCHLASID, @Grade 
   End

CLOSE Teacher_GradeCursor 
DEALLOCATE Teacher_GradeCursor 

--Add last teacher in cursor
Insert @TeacherGrades select @LastTCHLASID, @GradeString 


select * from @TeacherGrades