1

I have been pulling my hair out on this one. I'm not very adept at coding formulas.

I have a two column database that looks like this:

USERID1     1/1/2000
USERID1     2/1/2000
USERID1     3/3/2001
USERID2     1/5/2000
USERID2     2/7/2001
USERID2     3/30/2009
USERID2     3/10/2013
USERID3     2/5/2000
USERID3     3/7/2001
USERID3     5/30/2009
USERID3     9/10/2013

I think I need something to step through all of the rows that relate to USERID1 and write them all to one row:

USERID1 1/1/2000 2/1/2000 3/3/2001

Then move to the next USERID Value and write the associated dates:

USERID2 1/5/2000 2/7/2001 3/30/2009 3/10/2013

As seen above, the total number of records for each USERID value is different.

The results can be concatenated with commas, or separate fields.

I have tried using the "multiple columns" option in Section Expert, and have tried various concatenation formulas, but can't get the right syntax for the data types, and haven't seen how to loop through column A and change an array input when the value changes in column a.

nalzok
  • 14,965
  • 21
  • 72
  • 139
user2544132
  • 11
  • 1
  • 2
  • What database platform are you using? There is the listagg function in Oracle: http://dba.stackexchange.com/questions/20602/oracle-listagg-forces-sort-group-by-execution-plan – Sun Jul 09 '13 at 18:19

1 Answers1

3

Add a group on USERID.

Add this formula to the group's header section and suppress it:

// {@reset}
WhilePrintingRecords;
StringVar Array reset;
StringVar Array dates:=reset;
True; // return a dummy value

Add this formula to the detail section and suppress it:

// {@serialize}
WhilePrintingRecords;
StringVar Array dates;

Redim Preserve dates[Ubound(dates)+1];
dates[Ubound(dates)]:=ToText({table.date_field,"MM/dd/yyyy");

Add this formula to the group's footer section:

//{@display}
WhilePrintingRecords;
StringVar Array dates;
Join(dates, ",");
craig
  • 25,664
  • 27
  • 119
  • 205