HI,
I have two columns say carname and model.i want the data to be displayed in such a way that for every carname all the associated models should be displayed in one single row.
that is for example if the carname is bmw and it has say 3 models all the 3 models should be displayed in one row separated by commas.

CARNAME MODEL

BMW 325I,328,723

THANKS
MADDY

Maddy, this isn't as hard as you might think. I
just put this together in a few minutes. Have a
look:

-* Settings

SET ASNAMES = ON
SET HOLDLIST = PRINTONLY

-* Summarize to the level of detail required
-* first. Do this because you will end up with
-* potentially a bunch of detail lines that you
-* don't need.

TABLE FILE CAR
BY CAR
BY BODYTYPE
ON TABLE HOLD AS STEP1
END

-* LIST the bodytype by car so that associated
-* record number is created for each bodytype.

TABLE FILE STEP1
LIST BODYTYPE
BY CAR
ON TABLE HOLD AS STEP2
END

-* Create columns for each bodytype. You could
-* go as high as required and in fact, could have
-* this logic controlled by DM if you were to
-* determine the highest value of LIST in STEP2
-* so that it's totally dynamic. If you need
-* assistance with this, this should not be a
-* problem.

DEFINE FILE STEP2
BODYTYPE1/A12 = IF LIST EQ 1 THEN BODYTYPE ELSE ' ';
BODYTYPE2/A12 = IF LIST EQ 2 THEN BODYTYPE ELSE ' ';
BODYTYPE3/A12 = IF LIST EQ 3 THEN BODYTYPE ELSE ' ';
BODYTYPE4/A12 = IF LIST EQ 4 THEN BODYTYPE ELSE ' ';
END

-* Sum MAX values for each. You NEED to use MAX

TABLE FILE STEP2
SUM MAX.BODYTYPE1 MAX.BODYTYPE2 MAX.BODYTYPE3 MAX.BODYTYPE4
BY CAR
ON TABLE HOLD AS STEP3
END

-* Define the new bodytype field. Comma fields
-* (COM1,COM2,COM3) are required to ensure you
-* don't end up with a bunch of commas when
-* there is no data.

DEFINE FILE STEP3
COM1/A1 = IF BODYTYPE2 NE ' ' THEN ',' ELSE ' ';
COM2/A1 = IF BODYTYPE3 NE ' ' THEN ',' ELSE ' ';
COM3/A1 = IF BODYTYPE4 NE ' ' THEN ',' ELSE ' ';
NEWBODYTYPE/A51 = BODYTYPE1 || COM1 | BODYTYPE2 || COM2 | BODYTYPE3 || COM3 | BODYTYPE4;
END

-* Here you go

TABLE FILE STEP3
SUM NEWBODYTYPE
BY CAR
END

There may be other ways to do this more efficiently, but this will give you an idea as to how it can be done.

Ken

Does it matter if you have too many commas?

TABLE FILE CAR
LIST MODEL BY CAR
ON TABLE HOLD
END
TABLE FILE HOLD
SUM MODEL ACROSS LIST NOPRINT BY CAR
ON TABLE HOLD AS MYFILE FORMAT LOTUS
END

THANKS K.LANE FOR YOUR CODE IT WORKED WELL FOR ME AND ALSO THANKS STEVE FOR UR HELP BUT IWHEN I TRIED RUNNING UR CODE ITS GIVING ME ERROR FILE DESCRIPTION FOR MYFILE IS NOT FOUND MAY ITS NOT HOLDING THE DATA IN LOTUS FORMAT.DO WE ARE USING DEVSTUDIO 5.3.4 VERSION.
THANKS
MADDY