+4  A: 

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
This is exactly what I needed -- to rank them so I could pivot. Thank you very much!
Daniel
+1  A: 

The old school method is to use CASE expressions; since SQL Server 2005 you can use PIVOT.

CASE example:

SELECT t.studnumber,
       CASE WHEN t.teachernumber = 57315 THEN t.teachernumber ELSE NULL END AS TEACHER1,
       CASE WHEN t.teachernumber = 88115 THEN t.teachernumber ELSE NULL END AS TEACHER1

But dynamically placing a given teachernumber as teacher1/etc is an entirely different matter.

OMG Ponies
This is what pivot does under-the-hood w/o the "black magic" and special syntax...
Ben
+1  A: 

I second PIVOT.

Here are two interesting links with programmatic solutions to dynamic crosstab data in SQL.

http://www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

ezingano
Changed "dinamic" to "dynamic".
OMG Ponies
Thanks. English is not my first language and sometimes some typos slip in...
ezingano