SQL 在 MS Access 中透视数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16546305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivoting data in MS Access
提问by user2382144
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.
我创建了一个查询，用于提取他们在一个月内使用的学生 ID 和膳食项目。我想计算一个学生在一个月内吃的每个项目（早餐、午餐、小吃）的数量。
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.
数据透视表报告中似乎有太多数据无法处理，所以我希望有一个可以运行的 SQL 查询。
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;
Current output:
电流输出：
+-----+-----------+
| SID | Meal |
+-----+-----------+
| 001 | Lunch |
| 002 | Lunch |
| 003 | Breakfast |
| 004 | Snack |
| 005 | Lunch |
| 006 | Lunch |
| 001 | Breakfast |
| 003 | Snack |
| 004 | Breakfast |
+-----+-----------+
Here's how I'd like it to look:
这是我希望它的外观：
+-----+-----------+-------+---------+
| SID | Breakfast | Lunch | Snack |
+-----+-----------+-------+---------+
| 001 | 3 | 10 | 1 |
| 002 | 4 | 8 | 10 |
| 003 | 18 | 2 | 7 |
| 004 | 6 | 7 | 2 |
+-----+-----------+-------+---------+
回答by Taryn
You can pivot the data using TRANSFORM:
您可以使用 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;
