我通過連接來自 MS-Access DB 中的三個表的資料進行查詢
simsID Forename Surname Class AssessmentName Mark Percentage
1234 Joe Bloggs 13X Test1 20 50
1235 Fred Bloggs 13Y Test1 31 77.5
1234 Joe Bloggs 13X Test2 30 60
1235 Fred Bloggs 13Y Test2 10 20
1235 Fred Bloggs 13Y Test3 20 33.3333333333333
1234 Joe Bloggs 13X Test3 34 56.6666666666667
我想按如下方式顯示資料
ID Forename Surname Class Test1 Mark Test1 % Test2 Mark Test2 % Test3 Mark Test3 %
1234 Joe Bloggs 13X 20 50 30 60 34 56.6666666666667
1235 Fred Bloggs 13Y 31 77.5 10 20 20 33.3333333333333
我能看到的唯一方法是做兩個交叉表查詢，然后加入它們。AllStudentData_Marks 查詢
TRANSFORM Avg(AllStudentData.Mark) AS AvgOfMark
SELECT AllStudentData.simsID AS ID, AllStudentData.Forename AS Forename, AllStudentData.Surname AS Surname, AllStudentData.Class AS Class
FROM AllStudentData
GROUP BY AllStudentData.simsID, AllStudentData.Forename, AllStudentData.Surname, AllStudentData.Class
PIVOT AllStudentData.Assessments.AssessmentName & " Mark";
AllStudentData_Precentage 查詢
TRANSFORM Avg(AllStudentData.Percentage) AS AvgOfPercentage
SELECT AllStudentData.simsID AS ID, AllStudentData.Forename AS Forename, AllStudentData.Surname AS Surname, AllStudentData.Class AS Class
FROM AllStudentData
GROUP BY AllStudentData.simsID, AllStudentData.Forename, AllStudentData.Surname, AllStudentData.Class
PIVOT AllStudentData.Assessments.AssessmentName & " %";
加入查詢
SELECT AllStudentData_Marks.*, AllStudentData_Percentage.*
FROM AllStudentData_Marks INNER JOIN AllStudentData_Percentage ON AllStudentData_Marks.ID = AllStudentData_Percentage.ID;
正如預期的那樣，這給了我一張大桌子：
AllStudentData_Marks.ID AllStudentData_Marks.Forename AllStudentData_Marks.Surname AllStudentData_Marks.Class Test1 Mark Test2 Mark Test3 Mark AllStudentData_Percentage.ID AllStudentData_Percentage.Forename AllStudentData_Percentage.Surname AllStudentData_Percentage.Class Test1 % Test2 % Test3 %
1234 Joe Bloggs 13X 20 30 34 1234 Joe Bloggs 13X 50 60 56.6666666666667
1235 Fred Bloggs 13Y 31 10 20 1235 Fred Bloggs 13Y 77.5 20 33.3333333333333
我想限制列的數量（即不重復名稱）并使用更合理的列名稱（名字、姓氏等），但評估列的數量不固定，因此我在 SELECT 中使用通配符。
如何限制此最終查詢以僅回傳帶有合理標題的緊湊表？IE
ID Forename Surname Class Test1 Mark Test1 % Test2 Mark Test2 % Test3 Mark Test3 %
1234 Joe Bloggs 13X 20 50 30 60 34 56.6666666666667
1235 Fred Bloggs 13Y 31 77.5 10 20 20 33.3333333333333
提前感謝您的閱讀。
uj5u.com熱心網友回復：
是的，兩個連接的 CROSSTABS 是旋轉兩組值的一種方法。http://allenbrowne.com/ser-67.html#MultipleValues中描述的另一種方法。
還有一種方法涉及一個 UNION 查詢，然后將其用作 CROSSTAB 的源。使用您發布的示例資料集，請考慮：
查詢1：
SELECT simsID, Forename, Surname, Class, AssessmentName, Mark AS Data, "M" AS Cat FROM dataset
UNION SELECT simsID, Forename, Surname, Class, AssessmentName, Percentage, "P" AS Cat FROM dataset;
查詢2：
TRANSFORM Sum(Query1.Data) AS SumOfData
SELECT Query1.simsID, Query1.Surname, Query1.Class
FROM Query1
GROUP BY Query1.simsID, Query1.Forename, Query1.Surname, Query1.Class
PIVOT [AssessmentName] & [Cat];
可能會在多個資料點上找到感興趣的交叉表查詢
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/514542.html
標籤：毫秒访问数据透视表