MS Access交叉表查询具有多个列? [英] MS Access Crosstab query with multiple columns?

问题描述

在交叉表查询中获取多列的最佳方法是什么?我有下表:

What's the best way to get multiple columns in a crosstab query? I have the following table:

ID  Name    Topic   Date
123 John    Define  9/30/2015
123 John    Measure 10/30/2015
123 John    Analyze 11/30/2015
321 Mary    Measure 8/28/2015
321 Mary    Define  7/15/2015
321 Mary    Define  6/15/2015

这是我要寻找的结果:

ID  Name    Define  Define Date Measure Measure Date    Analyze Analyze Date
123 John       1      9/30/2015   1     10/30/2015        1     11/30/2015
321 Mary       2      7/15/2015   1     8/28/2015       

我创建了一个交叉表查询来旋转主题并按列计数，但是我还没有弄清楚如何为计数旁边的日期添加另一列.

I created a crosstab query to pivot the topics and count in columns but I have not figured out how to add another column for the date next to the count.

我可以有多个主题，日期不同，但是我将使用Max来获取最新日期.谢谢！

I could have multiple topics with different dates but I will use a Max to get the latest date. Thanks!

推荐答案

只需在GROUP BY查询中运行条件聚合即可转置数据.由于您不想将行值转置为列，因此此处的交叉表将无济于事.相反，您想根据行值定义列.

Simply run conditional aggregates in a GROUP BY query to transpose data. Crosstab here will not be helpful since you do not want to transpose row values to columns. Instead you want to define columns according to row values.

SELECT [ID], [Name], 
       Count(IIF(Topic='Define', Topic, Null)) As [Define], 
       Max(IIF(Topic='Define', [Date], Null)) As [Define Date], 
       Count(IIF(Topic='Measure', Topic, Null)) As [Measure],
       Max(IIF(Topic='Measure', [Date], Null)) As [Measure Date], 
       Count(IIF(Topic='Analyze', Topic, Null)) As [Analyze],
       Max(IIF(Topic='Analyze', [Date], Null)) As [Analyze Date]
FROM TableName
GROUP BY [ID], [Name];

这篇关于MS Access交叉表查询具有多个列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！