I'm sure this is very simple to do but I can't seem to find an explanation in the documentation or by searching.

I have a mysql table that looks like this:

ID POS TYPE
10 1 ABC
10 2 XYZ

And I would like the report to look like:
ID TYPE
10 ABC, XYZ

I've tried summing on the type field but that only returns the first value.

Thank you, any help would be appreciated!

This message has been edited. Last edited by: FP Mod Chuck,

Is output a concatenated list of TYPEs ?

Hi Waz, yes that is correct.

Here's out I do this:

TABLE FILE CAR
PRINT CAR
      COMPUTE SAME_COUNTRY/I1=COUNTRY EQ LAST COUNTRY; NOPRINT
      COMPUTE WK_CAR/A18=IF SAME_COUNTRY EQ 0 THEN CAR ELSE '; ' | CAR; NOPRINT
      COMPUTE CAR_LEN/I6=ARGLEN(17,WK_CAR,'I6'); NOPRINT
      COMPUTE TOTAL_LEN/I6=IF SAME_COUNTRY EQ 0 THEN CAR_LEN ELSE LAST TOTAL_LEN+CAR_LEN; NOPRINT
      COMPUTE ALL_CARS/A180=IF SAME_COUNTRY EQ 0 THEN WK_CAR ELSE OVRLAY(ALL_CARS,180,WK_CAR,CAR_LEN,LAST TOTAL_LEN+1,'A180');
   BY COUNTRY
   ON TABLE SET HOLDLIST PRINTONLY
   ON TABLE HOLD
END
TABLE FILE HOLD
  SUM LST.ALL_CARS
   BY COUNTRY
END

One thing to consider is the maximum number of CAR values per COUNTRY. This assumes a max of 10 CAR values per COUNTRY.

With the power and flexibility of the WebFOCUS language, there is often more than one way of meeting a requirement, giving the developer to freedom to choose what works given the data source, developer experience, maintainability, and so on.

Here is another example using the CAR database as a source:

TABLE FILE CAR
-* How many cars per country?
SUM CNT.CAR NOPRINT
BY  COUNTRY NOPRINT
-* 'Accumulate' car values.
PRINT   COUNTRY AS 'Country'
        CAR     NOPRINT
COMPUTE NEWCAR/A4096V = IF COUNTRY EQ LAST COUNTRY THEN (NEWCAR || ', ') | CAR  ELSE CAR; AS 'Car'
COMPUTE ROW_NUMBER/I5 = IF COUNTRY EQ LAST COUNTRY THEN ROW_NUMBER + 1          ELSE 1;   NOPRINT
BY      COUNTRY NOPRINT
BY      CAR     NOPRINT
-* Take only last row of each country.  
WHERE TOTAL CNT.CAR EQ ROW_NUMBER
END 

  Country     Car             
  -------     ---
  ENGLAND     JAGUAR, JENSEN, TRIUMPH 
  FRANCE      PEUGEOT 
  ITALY       ALFA ROMEO, MASERATI  
  JAPAN       DATSUN, TOYOTA             
  W GERMANY   AUDI, BMW