SQL Server:透视多个聚合
几个小时以来，我一直在寻找有关我的问题的答案.
I have been looking for an answer for a few hours about my problem.
我的当前表:
StudentName Course Correct Wrong Blank Score
-------------------------------------------------
Student1 Math 38 2 0 95
Student1 English 45 5 0 90
...
Student2 Math 38 2 0 95
Student2 English 45 5 0 90
我想要的是:
Math English
StudentName Correct Wrong Blank Score Correct Wrong Blank Score
Student1 38 2 0 95 45 5 0 90
Student2 38 2 0 95 45 5 0 90`
...
SELECT dbo.tbl_Students.StudentName,
dbo.tbl_CourseCategories.CourseCategory,
dbo.tbl_GeneralTestsScores.Correct,
dbo.tbl_GeneralTestsScores.Wrong,
dbo.tbl_GeneralTestsScores.NotAnswered,
dbo.tbl_GeneralTestsScores.Score
FROM
dbo.tbl_AcademicTermsStudents
INNER JOIN
dbo.tbl_Students ON dbo.tbl_AcademicTermsStudents.StudentID = dbo.tbl_Students.StudentID
INNER JOIN
dbo.tbl_GeneralTestsScores
INNER JOIN
dbo.tbl_CourseCategories
ON dbo.tbl_GeneralTestsScores.CourseCategoryID = dbo.tbl_CourseCategories.CourseCategoryID
ON dbo.tbl_AcademicTermsStudents.StudentID = dbo.tbl_GeneralTestsScores.StudentID
Order By StudentName
我搜索了很多页面，但都没有找到解决方案.
I searched many pages any could not end up with a solution.
谢谢.
我也接受以下作为解决方案...
I would also accept the following as a solution...
StudentName Math_C Math_W Math_B Math_S English_C English_W English_B English_S
Student1 38 2 0 95 45 5 0 90
Student2 38 2 0 95 45 5 0 90`
推荐答案
您可以通过在旋转之前为每个主题/分数组合添加一个新的唯一列来实现此目的.
You can achieve this with a 'double pivot' by adding a new unique column for each subject/score combination before pivoting.
这是一个静态示例，您可以轻松地将其转换为动态支点来满足更多类的需求.您还可以将原始查询放在 CTE 中，根据需要插入临时表或内联 - 为了清楚起见，我使用了单个临时表.
Here's a static example, you can easily turn this into a dynamic pivot to cater for more classes. You could also put your original query in a CTE, insert into a temp table or inline as required - I've used a single temp table for clarity.
希望这会有所帮助.
--Test Data
SELECT * INTO #Students FROM (VALUES
('Student1','Math', 38, 2, 0, 95),
('Student1','English', 45, 5, 0, 90),
('Student2','Math', 38, 2, 0, 95),
('Student2','English', 45, 5, 0, 90)
) A (StudentName, CourseName, Correct, Blank, Wrong, Score)
--Pivoting
SELECT StudentName
,SUM(Math_Correct) Math_Correct
,SUM(Math_Blank) Math_Blank
,SUM(Math_Wrong) Math_Wrong
,SUM(Math_Score) Math_Score
,SUM(English_Correct) English_Correct
,SUM(English_Blank) English_Blank
,SUM(English_Wrong) English_Wrong
,SUM(English_Score) English_Score
FROM
(SELECT
S.StudentName
,S.CourseName+'_Correct' CourseNameCorrrect
,S.CourseName+'_Blank' CourseNameBlank
,S.CourseName+'_Wrong' CourseNameWrong
,S.CourseName+'_Score' CourseNameScore
,S.Correct
,S.Blank
,S.Wrong
,S.Score
FROM #Students S ) S2
PIVOT( MAX(Correct) FOR CourseNameCorrrect IN ([Math_Correct], [English_Correct])) P1
PIVOT( MAX(Blank) FOR CourseNameBlank IN ([Math_Blank], [English_Blank])) P2
PIVOT( MAX(Wrong) FOR CourseNameWrong IN ([Math_Wrong], [English_Wrong])) P3
PIVOT( MAX(Score) FOR CourseNameScore IN ([Math_Score], [English_Score])) P4
GROUP BY StudentName
StudentName Math_Correct Math_Blank Math_Wrong Math_Score English_Correct English_Blank English_Wrong English_Score
----------- ------------ ----------- ----------- ----------- --------------- ------------- ------------- -------------
Student1 38 2 0 95 45 5 0 90
Student2 38 2 0 95 45 5 0 90
相关文章