复杂枢轴

Question

我已经减少一个复杂的模式的下列样品

学生

• StudentID INT，名称为varchar（50）
• 1，比尔
• 2，艾美
• 3，Beth
• 4，Scot吨
• 5，史蒂夫

类

• 的ClassID INT，名称为varchar（50），周期VARCHAR（50）
• 1，代数，PERIOD1
• 2 ，地理，Period3
• 3，生物学，Period5
• 4，物理，Period4
• 5，语音PERIOD2
• 6，历史，Period6

和结表

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

我的目标是按照时间顺序列出每个学生的选择课程。我有以下选择

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 

在我需要帮助，我需要导致

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 

是将所有非空向左列，因此不存在空白。列名可以被重新命名，例如

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

我可以通过在每一行/列用光标选择枢轴到一个临时表，然后迭代，但我想避免这样做。任何建议，非常感谢。

Answer 1

假设SQL Server 2005中（至少），使用ROW_NUMBER()订购的选择：

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