Pivot Query in MS Access

Question

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

And I would like to output it as follows:

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.

Answer 1

Data pivot requires 3 fields. In addition to ID and Cat, need a field that provides an identifier for each record in each ID/Name group. Other database platforms have intrinsic function to efficiently accomplish (e.g. SQLServer has ROW_NUMBER()). In Access use DCount() domain aggregate function.

Consider:

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;

Side note: The group sequence value can be calculated using a correlated subquery but CROSSTAB fails with that approach. Try it: save query object with following SQL and then reference that query as source in CROSSTAB.

SELECT Data.ID, Data.Name, Data.Cat, "Cat" & (
    SELECT Count(*) FROM Data AS Dupe 
    WHERE Dupe.ID=Data.ID AND Dupe.Cat<Data.Cat)+1 AS GrpSeq
FROM Data;