Denormalizing Data (Maybe A Pivot?)

Question

I have a simple table containing Student Numbers and corresponding Teacher Numbers, and I need to denormalize it for input to a legacy system.

For example, here's what the data looks like now:

StudNumber  TeacherNumber
445        57315
445        88584
445        95842
858        88115
858        65443
858        57315
858        21144
858        18657
858        88584
311        64521
665        35512
665        57315
665        15546

What I'd like it to look like is this, splitting out each Teacher into a separate column, filling columns from left-to-right. One business rule is that each student can have no more than six teachers:

StudNum Teacher1    Teacher2    Teacher3    Teacher4    Teacher5    Teacher6
445     57315       88584        95842
858     88115       65443        57315      21144        18657      88584
311     64521
665     35512       57315        15546

There are 10,000+ rows in the original table, so I need to do this programatically. Thank you!

Answer 1

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

Answer 2

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.

Answer 3

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/