在 MS Access 中透视数据
问题描述：
我创建了一个查询，用于提取他们在一个月内使用的学生 ID 和膳食项目.我想计算一个学生在一个月内吃的每个项目(早餐、午餐、小吃)的数量.
I have a query that I've created to pull student IDs and meal items they have taken over a month long period. I would like to count the numbers of each item (Breakfast, Lunch, Snack) taken by a student over the course of the month.
在数据透视表报告中似乎有太多数据无法处理，所以我希望有一个我可以运行的 SQL 查询.
It appears there's too much data for access to handle in a Pivot Table report, so I was hoping there was a SQL query I could run instead.
这是我创建的当前查询:
Here's the current query I've created:
SELECT April2013.SID, MenuItems.MealType AS Apr2013Meal
FROM April2013 LEFT JOIN MenuItems ON MenuItems.Item=April2013.Item;
当前输出:
+-----+-----------+
| SID | Meal |
+-----+-----------+
| 001 | Lunch |
| 002 | Lunch |
| 003 | Breakfast |
| 004 | Snack |
| 005 | Lunch |
| 006 | Lunch |
| 001 | Breakfast |
| 003 | Snack |
| 004 | Breakfast |
+-----+-----------+
这是我想要的样子:
+-----+-----------+-------+---------+
| SID | Breakfast | Lunch | Snack |
+-----+-----------+-------+---------+
| 001 | 3 | 10 | 1 |
| 002 | 4 | 8 | 10 |
| 003 | 18 | 2 | 7 |
| 004 | 6 | 7 | 2 |
+-----+-----------+-------+---------+
答
您可以使用 TRANSFORM 对数据进行透视:
You can pivot the data using TRANSFORM:
TRANSFORM COUNT(MenuItems.MealType)
SELECT April2013.SID, MenuItems.MealType
FROM April2013
LEFT JOIN MenuItems
ON MenuItems.Item=April2013.Item
GROUP BY April2013.SID
PIVOT MenuItems.MealType;