Complicated Pivot

Question

I have reduced a complex schema to the following samples

Students

• StudentID int, Name varchar(50)
• 1, Bill
• 2, Amy
• 3, Beth
• 4, Scott
• 5, Steve

Classes

• ClassID int, Name varchar(50), Period varchar(50)
• 1, Algebra, Period1
• 2, Geography, Period3
• 3, Biology, Period5
• 4, Physics, Period4
• 5, Speech, Period2
• 6, History, Period6

and a junction table

StudentsClasses

• StudentID int, ClassID int
• 1, 1
• 1, 4
• 1, 5
• 2, 6
• 3, 5
• 3, 4
• 3, 6
• 4, 1
• 4, 4
• 5, 5
• 5, 6

My goal is to list each student with their selected classes listed in period order. I have the following select

SELECT Name,Period1, Period2, Period3, 
    Period4, Period5, Period6  
    FROM (
SELECT _Students.Name AS [NAME],_Classes.Period AS PIVOT_CODE, _Classes.name as [Class] 
FROM _Classes 
    INNER JOIN _StudentsClasses ON _Classes.ClassID=_StudentsClasses.ClassID 
    INNER JOIN _Students ON _StudentsClasses.StudentID=_Students.StudentID
)
    AS data
    PIVOT 
    (  min([Class])  FOR [PIVOT_CODE] IN 
        (Period1, Period2, Period3, 
    Period4, Period5, Period6)
    ) AS pvt

Which results in

Name   Period1   Period2   Period3   Period4   Period5   Period6
------ --------- --------- --------- --------- --------- ----------
Amy    NULL      NULL      NULL      NULL      NULL      History
Beth   NULL      Speech    NULL      Physics   NULL      History
Bill   Algebra   Speech    NULL      Physics   NULL      NULL
Scott  Algebra   NULL      NULL      Physics   NULL      NULL
Steve  NULL      Speech    NULL      NULL      NULL      History

Where I need help is I need is to move all non-nulls towards the left column so there are no blanks. The column names can be renamed, for example

Name   Choice1   Choice2   Choice3   Choice4   Choice5   Choice6
------ --------- --------- --------- --------- --------- ----------
Amy    History
Beth   Speech    Physics   History
Bill   Algebra   Speech    Physics
Scott  Algebra   Physics
Steve  Speech    History

I can do this by selecting the pivot into a temp table then iterating over each row/column with a cursor but I'd like to avoid that. Any suggestions are greatly appreciated.

Answer 1

Assuming SQL Server 2005 (at least), using ROW_NUMBER() to order the choices:

SELECT Name, Choice1, Choice2, Choice3, Choice4, Choice5, Choice6  
FROM (
    SELECT 
        S.Name AS [NAME], 
        'Choice' + CAST(ROW_NUMBER() OVER(PARTITION BY S.Name ORDER BY S.Name, C.Period) AS VARCHAR) AS PIVOT_CODE, 
         C.Name as [Class]
    FROM Classes C
        JOIN StudentsClasses SC ON C.ClassID = SC.ClassID 
        JOIN Students S ON SC.StudentID = S.StudentID
    )
    AS data
    PIVOT 
    (  min([Class])  FOR [PIVOT_CODE] IN 
        (Choice1, Choice2, Choice3, Choice4, Choice5, Choice6)
    ) AS pvt