The simple answer is that this
can't be done in a query, because there isn't any way to aggregate the field values when the record structure is de-normalized like this. You could produce a query of several SELECT statements UNIONed together, but it would have to be "hard coded" to the field value of the manufacturer name to work, and if you added a
different manufacturer to the data (i.e. Packard), it wouldn't be picked up until you added
that name to the combination of SELECTs in the UNION.
Very messy.
The "one column of CAR" structure
will work, and I wonder why your table structure doesn't fit that design. If you really need to keep a unique key on each row, then expand the fields to add the secondary key, so that the data looks like this:
0001 001 HONDA
0002 001 PONTIAC
0003 001 FORD
0004 002 NISSAN
0005 002 TOYOTA
0006 002 HONDA
0007 003 MITSU
0008 003 MAZDA
0009 003 NISSAN
Without this record structure, you could do this through a
stored procedure that calculates the aggregates and returns a dataset of results (or creates a separate result table that can be queried). But this would just be a hard solution to a bad data design.
DBA
Print This
