Hi, I have the following scenario:
Table1:
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:
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
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