MS Access 中的透视查询 [英] Pivot Query in MS Access

问题描述

我在 Access 表中有以下数据:

I have the following data in an Access table:

ID    Name      CAT
1     Bill      Red
1     Bill      Yellow
1     Bill      Green
1     Bill      Orange
2     Ted       Purple
2     Ted       White
3     Alice     Indigo
3     Alice     Violet
3     Alice     Red

我想输出如下:

ID    Cat1    Cat2    Cat3    Cat4
1     Red     Yellow  Green   Orange
2     Purple  White        
3     Indigo  Violet  Red    

我可以为此使用枢轴吗?如果是这样，有人可以建议合适的查询吗?非常感谢.

Can I use pivot for this? If so, can someone suggest a suitable query? Many thanks.

推荐答案

考虑:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND Cat<'" & [Cat] & "'")+1;

或者如果有唯一的记录标识符字段 - 自动编号应该提供:

Or if there is a unique record identifier field - autonumber should serve:

TRANSFORM First(Data.Cat) AS FirstOfCat
SELECT Data.ID, Data.Name
FROM Data
GROUP BY Data.ID, Data.Name
PIVOT "Cat" & DCount("*","Data","ID=" & [ID] & " AND ID_PK<" & [ID_PK])+1;

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