You can use pivot. You also need to "Rank" your teachers 1-6. See my comment on how you want to do this. For now:
Select StudNumber, TeacherNumber, TeacherRank
from (
Select ST.StudNumber
, ST.TeacherNumber
, ROW_NUMBER() OVER (PARTITION BY ST.StudNumber
ORDER BY ST.TeacherNumber) AS TeacherRank
From StudentTeacher AS ST)
Where TeacherRank <=6
Then you can pivot on this statement. Here is a good explanation: Using Pivot and UnPivot
Jeff O
2009-11-02 20:44:02