I have a table that is set up like below:

COUNTRY SEQ_NO CAR RETAIL_COST
ENGLAND 1 JAGUAR 8,878
ENGLAND 2 JENSEN 17,850
ENGLAND 3 TRIUMPH 5,100
JAPAN 1 DATSUN 3,139
JAPAN 2 TOYOTA 3,339


Currently I have a report that does seven passes against this table and joins that data back to the original table request to get the data needed. This seems very time consuming process and it seems like there can be a better way.

I know you can do a table request like the following to get the all the data listed out in an easy manner, but I don’t have an easy way to use it in the report since the column headers are all the same names.

TABLE FILE CAR
SUM
CAR
RETAIL_COST
BY COUNTRY
ACROSS SEQ_NO NOPRINT
END

What I was wondering is if there was an easier way to be able to do one pass against the table and pull out the data that I need so the report isn't doing so many passes against the same table. I would love to be able to get the output like the following:

COUNTRY CAR1 RETAIL_COST1 CAR2 RETAIL_COST2 CAR3 RETAIL_COST3
ENGLAND JAGUAR 8,878 JENSEN 17,850 TRIUMPH 5,100
JAPAN DATSUN 3,139 TOYOTA 3,339


Does anyone know a way that this can be achieved?

This message has been edited. Last edited by: Kerry,

May be hold that main table data in a HOLD file with format FOCUS and index necessary fields. This way you will access the main table only once and report runs faster because of FOCUS database

Look up SET ASNAMES

quote:

Originally posted by Enigma006:
May be hold that main table data in a HOLD file with format FOCUS and index necessary fields. This way you will access the main table only once and report runs faster because of FOCUS database

I ended up doing this and saving the focus created master to take a look at it. Based on what focus created, it did exactly what I needed it to do with renaming the fields with the seq# concatinated to the field name. I just never had the new field names from the created hold file. Thanks for helping me through this guys.