Access sql fields to rows with aggregates

[JimmyBob]

Hi, I have the following scenario:

Table1:

ID	Code	Value
400	100	Red
400	101	Green
400	101	Green
400	102	Blue
400	103	Brown
400	103	Brown
400	103	Brown
401	100	Black
401	102	Yellow
401	103	Green

The above table has 3 fields: ID, Code and Value When codes appear multiple times for the same ID the corresponding Value will always be the same e.g. ID 400 has 3 entries for Code 103 but the Value is always Brown.

What I'd like to be able to do is to rearrange the above table to that below:

Table2:

ID	100	100Agg	101	101Agg	102	102Agg	103	103Agg
400	Red	1	Green	2	Blue	1	Brown	3
401	Black	1			Yellow	1	Green	1

In the above table I'd like to reduce each ID entry to one record and expanding this record to include all included Codes as separate fields, and, include a count of the number of times each code appears, followed by the colour Value of that code.

Is this doable in sql alone? I'm currently using MS Access to draft things however this will likely also be used with Oracle sql.

Thanks for any help

 

[Fazza]

you probably could do it, maybe a little messy & not an ideal thing to do though! untested. something like below. HTH

TRANSFORM MAX(Value)
SELECT ID
FROM
(SELECT DISTINCT ID, Code, Value
FROM table
UNION
SELECT ID, Code & 'Agg', COUNT(*)
FROM table
GROUP BY ID, Code & 'Agg')
GROUP BY ID
PIVOT Code

 

[JimmyBob]

Thanks.
Access 2003 (unfortunately a limitation of my workplace) reports 'Syntax error in FROM clause'
The example data above is heavily modified from the real data & table structure.

 

[Joe4]

Fazza's code worked for me without errors.
Are you sure that you changed the table and field name references in Fazza's code to match your names?

 

[JimmyBob]

Oops..apologies all round - forgot to rename a field from the source table.

This works great and will help a lot.
Many thanks Fazza.