Hello,

I am trying create the output like
('MAKE1','MAKE2','MAKE3') by company.

I have data stored in the database like
Company Make
Com1 Make1
Com1 Make2
Com1 Make3
Com2 Make1
Com2 Make2

How can achieve output string, tried already compute statement

TABLE FILE ABC
PRINT
COMPUTE
MK/A300= IF Make NE LAST Make THEN ( Make || ',' MK);
BY Company
END


Any idea will be or suggestion what I am doing wrong. Thanks in advance for your suggestions/inputs.

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

Viral,

Do you want the parentheses, the commas and the quotes also or just the values on one line?

Are you trying to create a SQL statement? I recently did this one so I could read a focus table and pull the formats and date for one line and put it into a SQL statement. The output is just html and I cut and paste it, but I guess you could adapt it to do multiple records. This table has 55 columns so the code is kinda long.

 -** GENSQL to generate SQL statement from FRPRPTS table
-** so that it can be duplicated for install pkg
-*----------------------------------------------------------------------
-SET &RPT_ID = 50;
CHECK FILE FRPRPTS HOLD
TABLE FILE HOLD
 PRINT FIELDNAME 
ON TABLE SAVE AS RFIELDS FORMAT ALPHA
END
-RUN
-READ RFIELDS, &f1,&f2,&f3,&f4,&f5,&f6,
- &f7,&f8,&f9,&f10,&f11,&f12,&f13,&f14,&f15,&f16,&f17, 
- &f18,&f19,&f20,&f21,&f22,&f23,&f24,&f25,&f26,&f27, 
- &f28,&f29,&f30,&f31,&f32,&f33,&f34,&f35,&f36,&f37,
- &f38,&f39,&f40,&f41,&f42,&f43,&f44,&f45,&f46,&f47,&f48,&f49,
- &f50,&f51,&f52,&f53,&f54,&f55
-RUN
-SET &A='&f1|,&f2|,&f3|,&f4|,&f5|,&f6|,&f7|,&f8|,&f9|,&f10|,&f11|,&f12|,';
-SET &B='&f13|,&f14|,&f15|,&f16|,&f17|,&f18|,&f19|,&f20|,&f21|,&f22|,&f23|,';
-SET &C='&f24|,&f25|,&f26|,&f27|,&f28|,&f29|,&f30|,&f31|,&f32|,&f33|,&f34|,';
-SET &D='&f35|,&f36|,&f37|,&f38|,&f39|,&f40|,&f41|,&f42|,&f43|,&f44|,&f45|,';
-SET &E='&f46|,&f47|,&f48|,&f49|,&f50|,&f51|,&f52|,&f53|,&f54|,&f55';

-SET &F='&A.EVAL&B.EVAL&C.EVAL&D.EVAL&E.EVAL';
-SET &G='insert into FRPRPTS (';
-SET &H=') values (';

-*********************************************

TABLE FILE HOLD
 PRINT FORMAT
ON TABLE SAVE AS RFORMATS FORMAT ALPHA
END
-RUN
-READ RFORMATS, &i1,&i2,&i3,&i4,&i5,&i6,
- &i7,&i8,&i9,&i10,&i11,&i12,&i13,&i14,&i15,&i16,&i17, 
- &i18,&i19,&i20,&i21,&i22,&i23,&i24,&i25,&i26,&i27,&i28,
- &i29,&i30,&i31,&i32,&i33,&i34,&i35,&i36,&i37,&i38,&i39,
- &i40,&i41,&i42,&i43,&i44,&i45,&i46,&i47,&i48,&i49,
- &i50,&i51,&i52,&i53,&i54,&i55
-RUN
-SET &c1=IF EDIT(&i1,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c2=IF EDIT(&i2,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c3=IF EDIT(&i3,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c4=IF EDIT(&i4,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c5=IF EDIT(&i5,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c6=IF EDIT(&i6,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c7=IF EDIT(&i7,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c8=IF EDIT(&i8,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c9=IF EDIT(&i9,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c10=IF EDIT(&i10,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c11=IF EDIT(&i11,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c12=IF EDIT(&i12,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c13=IF EDIT(&i13,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c14=IF EDIT(&i14,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c15=IF EDIT(&i15,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c16=IF EDIT(&i16,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c17=IF EDIT(&i17,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c18=IF EDIT(&i18,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c19=IF EDIT(&i19,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c20=IF EDIT(&i20,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c21=IF EDIT(&i21,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c22=IF EDIT(&i22,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c23=IF EDIT(&i23,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c24=IF EDIT(&i24,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c25=IF EDIT(&i25,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c26=IF EDIT(&i26,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c27=IF EDIT(&i27,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c28=IF EDIT(&i28,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c29=IF EDIT(&i29,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c30=IF EDIT(&i30,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c31=IF EDIT(&i31,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c32=IF EDIT(&i32,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c33=IF EDIT(&i33,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c34=IF EDIT(&i34,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c35=IF EDIT(&i35,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c36=IF EDIT(&i36,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c37=IF EDIT(&i37,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c38=IF EDIT(&i38,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c39=IF EDIT(&i39,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c40=IF EDIT(&i40,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c41=IF EDIT(&i41,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c42=IF EDIT(&i42,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c43=IF EDIT(&i43,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c44=IF EDIT(&i44,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c45=IF EDIT(&i45,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c46=IF EDIT(&i46,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c47=IF EDIT(&i47,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c48=IF EDIT(&i48,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c49=IF EDIT(&i49,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c50=IF EDIT(&i50,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c51=IF EDIT(&i51,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c52=IF EDIT(&i52,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c53=IF EDIT(&i53,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c54=IF EDIT(&i54,'9') EQ 'A' THEN 'Y' ELSE ' ';
-SET &c55=IF EDIT(&i55,'9') EQ 'A' THEN 'Y' ELSE ' ';
-RUN

DEFINE FILE FRPRPTS
cp/A1=')';
c0/A1=',';
S2/A73='&A.EVAL';
S3/A120='&B.EVAL';
S4/A108='&C.EVAL';
S5/A132='&D.EVAL';
S6/A112='&E.EVAL';
S7/A10=') values (';
-** set delimeters for alpha or integer
c1/A1=IF '&c1' EQ 'Y' THEN '''' ELSE '';
c2/A1=IF '&c2' EQ 'Y' THEN '''' ELSE '';
c3/A1=IF '&c3' EQ 'Y' THEN '''' ELSE '';
c4/A1=IF '&c4' EQ 'Y' THEN '''' ELSE '';
c5/A1=IF '&c5' EQ 'Y' THEN '''' ELSE '';
c6/A1=IF '&c6' EQ 'Y' THEN '''' ELSE '';
c7/A1=IF '&c7' EQ 'Y' THEN '''' ELSE '';
c8/A1=IF '&c8' EQ 'Y' THEN '''' ELSE '';
c9/A1=IF '&c9' EQ 'Y' THEN '''' ELSE '';
c10/A1=IF '&c10' EQ 'Y' THEN '''' ELSE '';
c11/A1=IF '&c11' EQ 'Y' THEN '''' ELSE '';
c12/A1=IF '&c12' EQ 'Y' THEN '''' ELSE '';
c13/A1=IF '&c13' EQ 'Y' THEN '''' ELSE '';
c14/A1=IF '&c14' EQ 'Y' THEN '''' ELSE '';
c15/A1=IF '&c15' EQ 'Y' THEN '''' ELSE '';
c16/A1=IF '&c16' EQ 'Y' THEN '''' ELSE '';
c17/A1=IF '&c17' EQ 'Y' THEN '''' ELSE '';
c18/A1=IF '&c18' EQ 'Y' THEN '''' ELSE '';
c19/A1=IF '&c19' EQ 'Y' THEN '''' ELSE '';
c20/A1=IF '&c20' EQ 'Y' THEN '''' ELSE '';
c21/A1=IF '&c21' EQ 'Y' THEN '''' ELSE '';
c22/A1=IF '&c22' EQ 'Y' THEN '''' ELSE '';
c23/A1=IF '&c23' EQ 'Y' THEN '''' ELSE '';
c24/A1=IF '&c24' EQ 'Y' THEN '''' ELSE '';
c25/A1=IF '&c25' EQ 'Y' THEN '''' ELSE '';
c26/A1=IF '&c26' EQ 'Y' THEN '''' ELSE '';
c27/A1=IF '&c27' EQ 'Y' THEN '''' ELSE '';
c28/A1=IF '&c28' EQ 'Y' THEN '''' ELSE '';
c29/A1=IF '&c29' EQ 'Y' THEN '''' ELSE '';
c30/A1=IF '&c30' EQ 'Y' THEN '''' ELSE '';
c31/A1=IF '&c31' EQ 'Y' THEN '''' ELSE '';
c32/A1=IF '&c32' EQ 'Y' THEN '''' ELSE '';
c33/A1=IF '&c33' EQ 'Y' THEN '''' ELSE '';
c34/A1=IF '&c34' EQ 'Y' THEN '''' ELSE '';
c35/A1=IF '&c35' EQ 'Y' THEN '''' ELSE '';
c36/A1=IF '&c36' EQ 'Y' THEN '''' ELSE '';
c37/A1=IF '&c37' EQ 'Y' THEN '''' ELSE '';
c38/A1=IF '&c38' EQ 'Y' THEN '''' ELSE '';
c39/A1=IF '&c39' EQ 'Y' THEN '''' ELSE '';
c40/A1=IF '&c40' EQ 'Y' THEN '''' ELSE '';
c41/A1=IF '&c41' EQ 'Y' THEN '''' ELSE '';
c42/A1=IF '&c42' EQ 'Y' THEN '''' ELSE '';
c43/A1=IF '&c43' EQ 'Y' THEN '''' ELSE '';
c44/A1=IF '&c44' EQ 'Y' THEN '''' ELSE '';
c45/A1=IF '&c45' EQ 'Y' THEN '''' ELSE '';
c46/A1=IF '&c46' EQ 'Y' THEN '''' ELSE '';
c47/A1=IF '&c47' EQ 'Y' THEN '''' ELSE '';
c48/A1=IF '&c48' EQ 'Y' THEN '''' ELSE '';
c49/A1=IF '&c49' EQ 'Y' THEN '''' ELSE '';
c50/A1=IF '&c50' EQ 'Y' THEN '''' ELSE '';
c51/A1=IF '&c51' EQ 'Y' THEN '''' ELSE '';
c52/A1=IF '&c52' EQ 'Y' THEN '''' ELSE '';
c53/A1=IF '&c53' EQ 'Y' THEN '''' ELSE '';
c54/A1=IF '&c54' EQ 'Y' THEN '''' ELSE '';
c55/A1=IF '&c55' EQ 'Y' THEN '''' ELSE '';
END
-RUN

TABLE FILE FRPRPTS
HEADING
"SQL Statement for <RPT_ID <RPT_NAME : "
PRINT COMPUTE S1/A21='insert into FRPRPTS (';
S2 S3 S4 S5 S6 S7
c1 &f1.EVAL c1 c0 OVER
c2 &f2.EVAL c2 c0 OVER
c3 &f3.EVAL c3 c0 OVER
c4 &f4.EVAL c4 c0 OVER
c5 &f5.EVAL c5 c0 OVER
c6 &f6.EVAL c6 c0 OVER
c7 &f7.EVAL c7 c0 OVER
c8 &f8.EVAL c8 c0 OVER
c9 &f9.EVAL c9 c0 OVER
c10 &f10.EVAL c10 c0 OVER
c11 &f11.EVAL c11 c0 OVER
c12 &f12.EVAL c12 c0 OVER
c13 &f13.EVAL c13 c0 OVER
c14 &f14.EVAL c14 c0 OVER
c15 &f15.EVAL c15 c0 OVER
c16 &f16.EVAL c16 c0 OVER
c17 &f17.EVAL c17 c0 OVER
c18 &f18.EVAL c18 c0 OVER
c19 &f19.EVAL c19 c0 OVER
c20 &f20.EVAL c20 c0 OVER
c21 &f21.EVAL c21 c0 OVER
c22 &f22.EVAL c22 c0 OVER
c23 &f23.EVAL c23 c0 OVER
c24 &f24.EVAL c24 c0 OVER
c25 &f25.EVAL c25 c0 OVER
c26 &f26.EVAL c26 c0 OVER
c27 &f27.EVAL c27 c0 OVER
c28 &f28.EVAL c28 c0 OVER
c29 &f29.EVAL c29 c0 OVER
c30 &f30.EVAL c30 c0 OVER
c31 &f31.EVAL c31 c0 OVER
c32 &f32.EVAL c32 c0 OVER
c33 &f33.EVAL c33 c0 OVER
c34 &f34.EVAL c34 c0 OVER
c35 &f35.EVAL c35 c0 OVER
c36 &f36.EVAL c36 c0 OVER
c37 &f37.EVAL c37 c0 OVER
c38 &f38.EVAL c38 c0 OVER
c39 &f39.EVAL c39 c0 OVER
c40 &f40.EVAL c40 c0 OVER
c41 &f41.EVAL c41 c0 OVER
c42 &f42.EVAL c42 c0 OVER
c43 &f43.EVAL c43 c0 OVER
c44 &f44.EVAL c44 c0 OVER
c45 &f45.EVAL c45 c0 OVER
c46 &f46.EVAL c46 c0 OVER
c47 &f47.EVAL c47 c0 OVER
c48 &f48.EVAL c48 c0 OVER
c49 &f49.EVAL c49 c0 OVER
c50 &f50.EVAL c50 c0 OVER
c51 &f51.EVAL c51 c0 OVER
c52 &f52.EVAL c52 c0 OVER
c53 &f53.EVAL c53 c0 OVER
c54 &f54.EVAL c54 c0 OVER
c55 &f55.EVAL c55 cp

WHERE RPT_ID EQ &RPT_ID
ON TABLE PCHOLD AS VALS FORMAT ALPHA
END
-RUN

 

forgot to say, this code does read the table first to find the field format and place parenthesis around alpa fields and not around numbers; so if you add or delete the variables to the right number of columns it should work for most tables. Sorry I didnt make the variables more descriptive, I was in a geeky mood that day

How about

-*new file
DEFINE FILE CAR
 BCAR/A500 = IF COUNTRY NE LAST COUNTRY
             THEN '(''' || CAR || ''''
             ELSE SUBSTR(500,LAST BCAR,1,481,481,'A481') || ',''' || CAR || '''' ;
 Cars/A501 = BCAR || ')' ;
END
TABLE FILE CAR
SUM LST.Cars
BY COUNTRY
END

..standard ACROSS method
DEFINE FILE WHATEVER
THING/I1=IF Company EQ LAST Company THEN LAST THING + 1 ELSE 1 ;
END
TABLE FILE WHATEVER
SUM MAKE ACROSS THING AS ''
BY Company
END
... or , taking you literally,
MK/A300= IF Make NE LAST Make THEN ( Make || ',' LAST MK) ELSE Make;
..now you have the challenge of just printing 1 for each Co. You can handle that?

Thanks for response, I was out for day, But I will try it out and let you know results looks like Waz solution will work.

Thank you all for your suggestions.

Sorry for delay, out of most of the week, tried the Waz solution and it worked for me.

Once again thanks all for the suggestions.