Collapse Blank Columns For Each Row

Question

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       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!

Answer 1

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