Is there a way to combine the data to a single row based on some other sort values.
I have data like this:
Key Month
1 2
1 4
1 6
2 3
4 99

And I wat to show it like this
Key Month
1 2, 4, 6
2 3
4 1-12

Thank you very much

Use Defined fields to define your new fields as concatenated fields based on certain conditons.

Here's an example from CAR:
DEFINE FILE CAR
CONCAT_STR/A200V=IF COUNTRY EQ LAST COUNTRY THEN (CONCAT_STR ||', '|| CAR ) ELSE CAR;
END
TABLE FILE CAR
SUM CONCAT_STR
BY COUNTRY
END

This assumes that the total length of the concatenated values will never be more than 200, but you can change that based on your need. It also assumes that data is already in order of Sort field (COUNTRY in this case)

You get
COUNTRY CONCAT_STR
ENGLAND JAGUAR,JENSEN,TRIUMPH
FRANCE PEUGEOT
ITALY ALFA ROMEO,MASERATI
JAPAN DATSUN,TOYOTA
W GERMANY AUDI,BMW

This message has been edited. Last edited by: Darin Lee,

Here is an example from one of my apps, using a counter. This concatentates a list of up to ten policy riders together, separated by a space.
DEFINE FILE HLDRIDER
COUNTER/I2=IF POL_NB EQ LAST POL_NB THEN COUNTER + 1 ELSE 1;
RDRSTRING/A65=IF POL_NB EQ LAST POL_NB THEN RDRSTRING ELSE '';
RDRSTRING/A65=
IF COUNTER EQ 1 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 1, RDRSTRING) ELSE
IF COUNTER EQ 2 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 7, RDRSTRING) ELSE
IF COUNTER EQ 3 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 13, RDRSTRING) ELSE
IF COUNTER EQ 4 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 19, RDRSTRING) ELSE
IF COUNTER EQ 5 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 25, RDRSTRING) ELSE
IF COUNTER EQ 6 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 31, RDRSTRING) ELSE
IF COUNTER EQ 7 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 37, RDRSTRING) ELSE
IF COUNTER EQ 8 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 43, RDRSTRING) ELSE
IF COUNTER EQ 9 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 49, RDRSTRING) ELSE
IF COUNTER EQ 10 THEN OVRLAY(RDRSTRING, 65, RDR_FORM_NB, 5, 55, RDRSTRING) ELSE
'MORE';
END
TABLE FILE HLDRIDER
SUM
RDRSTRING
BY
POL_NB
WHERE COUNTER LE 10;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS HLDRIDER_LTC FORMAT FOCUS INDEX POL_NB

Usernani,
Assuming that the field MONTH is I2 and takes values from 1 to 12 and also 99.

DEFINE FILE ...
AMONTH/A4=IF MONTH EQ 99 THEN '1-12' ELSE FTOA(MONTH,'(D4c)','A4');
END
TABLE FILE ...
LIST AMONTH BY KEY
ON TABLE HOLD
END
TABLE FILE HOLD
SUM AMONTH
BY KEY
ACROSS LIST NOPRINT
END