Access sql fields to rows with aggregates

JimmyBob

New Member
Joined
Sep 25, 2011
Messages
33
Hi, I have the following scenario:

Table1:

Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,352
you probably could do it, maybe a little messy & not an ideal thing to do though! untested. something like below. HTH

Code:
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

New Member
Joined
Sep 25, 2011
Messages
33
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.
 
Last edited:

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,444
Office Version
365
Platform
Windows
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

New Member
Joined
Sep 25, 2011
Messages
33
Oops..apologies all round - forgot to rename a field from the source table.

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

Watch MrExcel Video

Forum statistics

Threads
1,099,505
Messages
5,469,024
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top