复杂的轴心
|
我将复杂的架构简化为以下示例
学生们
StudentID int，名称varchar（50）
1，比尔
2，艾米
3，贝丝
4，斯科特
5，史蒂夫
班级
ClassID int，名称varchar（50），句点varchar（50）
1，代数，期间1
2，地理位置，期间3
3，生物学，期间5
4，物理，期间4
5，演讲，期间2
6，历史，时期6
和一个联结表
学生班
StudentID int，ClassID int
1、1
一四
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
没有找到相关结果
已邀请:
1 个回复
bab
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