Table linking options

[thisisboni]

Hi:

I have 2 tables (t1, t2) its a 1-to-many relation

can I join the 2 tables and display all results one row per each entry in t1 ?
eg.

t1 t2
--- ----
1 1, sam
2 1, bam
1, ham
2, lab

I want the output as

1 sam, bam, ham
2 lab

any help /suggestion would be appreciated !

tx

p.s.: I am on CR10 with Oracle 9i dB

 

[lbass]

Please review your example and fix it if necessary, as it doesn't really make sense--there appear to be missing values for the last two. There also appear to be three fields, but you have identified only two.

-LB

 

[thisisboni]

oops - sorry - I guess the formatting was messed up once posted :
lemme see if its ok this time:

t1: {1,2}
t2: {(1, sam),(1, bam),(1,ham),(2,lab)}


I want the output as

1 sam, bam, ham
2 lab



tx!

 

[lbass]

Join the two tables on the numeric field, and then insert a group on {t1.number}. Let's call your other field {table.name} (in the future, please name your fields). Then create three formulas:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar x := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar x := x + {table.name} + ", ";

//{@display} to be placed in the group footer;
whileprintingrecords;
stringvar x;
if len(x) > 2 then
left(x, len(x)-2)

Suppress the group header and details section, and drag the groupname into the group footer.

-LB