Hi all, I'm drawing a blank. I'm sure I've done this before, but I can't remember how. Basically I need a segment of columns to be merged to one column. For example:

TABLE FILE CARDATA
PRINT 
CAR1
CAR2
CAR3
BY YEAR
WHERE YEAR EQ '2010'
END

Would look something like this:

YEAR CAR1 CAR2 CAR3
2010 VW BMW FORD

How do I get it to look like this?

YEAR HEADER CAR
2010 CAR1 VW
2010 CAR2 BMW
2010 CAR3 FORD


I might add that I have used the OVER command but it doesn't seem to be keeping layout after I HOLD it and report from the HOLD file.

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

The McGyver technique perhaps?

It would allow you to "multiply" your record as many times as needed (3 in your case). You can then DEFINE a virtual field which would take the value in CAR1, CAR2 or CAR3 depending on the record COUNTER.

And your are correct about the OVER command. It is for display only. It doesn't translate into anything at all for a hold file. There are MANY references in the forum with regard the the McGyver technique referenced by nsjden, which is exactly what you need to convert columns to rows.

If you have a specific number of 'CAR' columns, you might be able to do something like this:

DEFINE FILE CARDATA
 HEAD1/A4 = 'CAR1';
 HEAD2/A4 = 'CAR2';
 HEAD3/A4 = 'CAR3';
END
-*
MATCH FILE CARDATA
 BY YEAR
 BY HEAD1 AS 'HEADER'
 BY CAR1  AS 'CAR'
RUN
FILE CARDATA
 BY YEAR
 BY HEAD2 AS 'HEADER'
 BY CAR2  AS 'CAR'
 AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE CARDATA
 BY YEAR
 BY HEAD3 AS 'HEADER'
 BY CAR3  AS 'CAR'
 AFTER MATCH HOLD OLD-OR-NEW
END

Thanks all. Both the Mcgyver method and Dan's MATCH method worked.

I appreciate your assistance!

Alternate master with OCCURS would work as well.