SQL Server：数据透视多个聚合 [英] SQL Server : Pivot Multiple Aggregates
本文介绍了SQL Server：数据透视多个聚合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我一直在寻找关于我问题的几个小时的答案。
我当前的表：
StudentName课程更正错误的空白分数
----------------------------- --------------------
Student1 Math 38 2 0 95
Student1 English 45 5 0 90
...
Student2 Math 38 2 0 95
Student2 English 45 5 0 90
：
数学英语
学生名称正确错误空白分数正确错误空白分数
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
按学生姓名排序
我搜索了许多页面，但最终找不到解决方案。
p>
编辑：我也接受以下作为解决方案...
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`
解决方案您可以通过在双枢轴之前为每个主题/分数组合添加一个新的唯一列。
这是一个静态示例，您可以轻松地将其转换为动态枢纽以满足更多类。您还可以将原始查询放在CTE中，根据需要插入到临时表或内联中 - 为了清楚起见，我使用了一个临时表。
希望这有助于。
- 测试数据
SELECT * INTO #Students FROM（VALUES
（'Student1'， 'Math'，38,2,0,95），
（'Student1'，'English'，45,5,0,90），
（'Student2'，'English'，45，5，0，90）
）A（学生姓名，课程名称，正确，空白，错误，分数）
- Pivoting
SELECT StudentName
，SUM（Math_Correct）Math_Correct
，SUM（Math_Blank）Math_Blank
，SUM（Math_Wrong）Math_Wrong
， （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 b $ b（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＃学生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
I have been looking for an answer for a few hours about my problem.
My current table:
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 90What I want is:
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 StudentNameI searched many pages any could not end up with a solution.
Thanks.
Edit: 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.
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.
Hope this helps.
--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
这篇关于SQL Server：数据透视多个聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
