Collapse Blank Columns For Each Row

I have a large table I imported from an Excel spreadsheet. For five of the columns, containing teacher names, I'd like to collapse all of the blanks in each row so I have no blanks to the left of any name. An example would probably show better what I mean.

Here's an example of the data now:

Student ID  Teacher1  Teacher2  Teacher3  Teacher4  Teacher5
445633      Mary                Deb       Juan  
876302      Trevor    Juan      Mary      Val       Deb
659404      Luo                                     Trevor
535485                          Deb    
775431                Val                           Luo
565002                Luo     开发者_JAVA技巧  Mary                Juan

And I'd like to collapse the blanks to the left, so it looks like this:

Student ID  Teacher1  Teacher2  Teacher3  Teacher4  Teacher5
445633      Mary      Deb       Juan    
876302      Trevor    Juan      Mary      Val       Deb
659404      Luo       Trevor      
535485      Deb        
775431      Val       Luo      
565002      Luo       Mary      Juan 

I have to do it this way because I'll pasting it into a state-mandated Excel template, and I can't have any blanks to the left of teacher names, only blanks to the right.

Thank you!

You can Unpivot, ROW_NUMBER() over Teacher and Pivot again... that is:

declare @t1 as table (StudentID int, Teacher1 nvarchar(100), Teacher2 nvarchar(100), Teacher3 nvarchar(100), Teacher4 nvarchar(100), Teacher5 nvarchar(100));
insert into @t1 values (445633,'Mary',NULL,'Deb','Juan',NULL),(876302,'Trevor','Juan','Mary','Val','Deb'),
(659404,'Luo',NULL,NULL,NULL,'Trevor'),(535485,NULL,NULL,NULL,'Deb',NULL),
(775431,NULL,NULL,'Val',NULL,'Luo'),(565002,NULL,NULL,'Luo','Mary','Juan');

with UnpivotT as (
    SELECT StudentID, TeacherName, TeacherColumn as OriginalColumn
    from @t1 unpivot (
        TeacherName for TeacherColumn in ([Teacher1],[Teacher2],[Teacher3],[Teacher4],[Teacher5])
    ) uT
), NewColumnT as (
    select StudentID, TeacherName,  'Teacher'+(
            convert(nvarchar(100), ROW_NUMBER() OVER (PARTITION BY StudentID ORDER BY OriginalColumn))
        ) as NewColumn
    from UnpivotT
)
select * 
from NewColumnT
pivot (
    max(TeacherName) for NewColumn in ([Teacher1],[Teacher2],[Teacher3],[Teacher4],[Teacher5])
) as pivotT