Access中动态列的行 [英] Rows to Dynamic columns in Access

问题描述

我需要在Access中进行设置，其中将表中的某些行转换为列...例如，假设我有此表:

I need a setup in Access where some rows in a table are converted to columns...for example, lets say I have this table:

Team    Employee    DaysWorked
Sales    John         23
Sales    Mark         3
Sales    James        5

然后通过使用查询/表单/其他方式，我希望显示以下内容:

And then through the use of a query/form/something else, I would like the following display:

Team   John   Mark   James
Sales   23     3       5

这种行到列的转换必须是动态的，因为一个团队可以有任意数量的员工，而员工可以更改，等等.有人可以指导我实现这一目标的最佳方法吗?

This conversion of rows to columns would have to be dynamic as a team could have any number of Employees and the Employees could change etc. Could anyone please guide me on the best way to achieve this?

推荐答案

您要创建CrossTab查询.这是您可以使用的SQL.

You want to create a CrossTab query. Here's the SQL that you can use.

TRANSFORM SUM(YourTable.DaysWorked) AS DaysWorked
SELECT YourTable.Team
FROM YourTable
GROUP BY YourTable.Team
PIVOT YourTable.Employee

当然，输出的不同之处在于列是按字母顺序排列的.

Of course the output is slightly different in that the columns are in alphabetical order.

Team   James  John  Mark
Sales  5      23    3

有关更多详细信息，请参见通过office.microsoft.com

For more detail see Make summary data easier to read by using a crosstab query at office.microsoft.com

这篇关于Access中动态列的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！