Comma Delimited List From a Column

[HMSS]

Hi,

With a FOR EACH loop, I need to create a Comma Delimited List From a Column. For example, from the following data:

MemberID Team FirstName
1 1 Jim
2 1 Mary
3 1 Bob
4 2 Sue
5 2 Ralph
6 2 Ellen
7 3 Bill
8 3 Linda

the desired output is a list of Teams and Team members, in the form of:

Team Members
1 Bob, Jim, Mary
2 Ellen, Ralph, Sue
3 Bill, Linda

Any helps anybody can give would be very much appreciated.

Thanks in advance

 

[StuartT]

Here is probably the simplest way of doing it, though depending on volume of data it could be quite slow:

output to "<output file name>".
FOR EACH <table-name> BREAK BY <table-name>.team BY tt-<table-name>.name:

IF FIRST-OF(<table-name>.team) THEN
PUT <table-name>.team " " <table-name>.name .
ELSE
PUT "," <table-name>.name.
IF LAST-OF(<table-name>.team) THEN
PUT SKIP.


END.

 

[palthe]

Uhm, I would do something like this:

/*
Creation and filling of temp-table, not of interest for the endresult
*/
DEFINE VARIABLE htemptable AS HANDLE NO-UNDO.
DEFINE VARIABLE hbuftemptable AS HANDLE NO-UNDO.
CREATE TEMP-TABLE htemptable.
htemptable:ADD-NEW-FIELD("MemberID","integer").
htemptable:ADD-NEW-FIELD("TeamID","integer").
htemptable:ADD-NEW-FIELD("Membername","character").
htemptable:TEMP-TABLE-PREPARE("tempmembers").
hbuftemptable = htemptable:DEFAULT-BUFFER-HANDLE.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DO i = 1 TO 8:
    hbuftemptable:BUFFER-CREATE.
    hbuftemptable:BUFFER-FIELD(1):BUFFER-VALUE = i.
    hbuftemptable:BUFFER-FIELD(2):BUFFER-VALUE = ENTRY(i,"1,1,1,2,2,2,3,3").
    hbuftemptable:BUFFER-FIELD(3):BUFFER-VALUE = ENTRY(i,"Jim,Mary,Bob,Sue,Ralph,Ellen,Bill,Linda").
END.
/* 
One solution to your problem:
*/
DEFINE VARIABLE hquery AS HANDLE NO-UNDO.
DEFINE VARIABLE cstring AS CHARACTER NO-UNDO.
i = 0.
cstring = "".
CREATE QUERY hquery.
hquery:SET-BUFFERS(hbuftemptable).
hquery:QUERY-PREPARE("for each tempmembers").
hquery:QUERY-OPEN.
hquery:GET-FIRST(NO-LOCK).
DO WHILE NOT hquery:QUERY-OFF-END:
    IF i <> hbuftemptable:BUFFER-FIELD("teamid"):BUFFER-VALUE THEN
        cstring = cstring + CHR(1) + hbuftemptable:BUFFER-FIELD(2):BUFFER-VALUE + " " + hbuftemptable:BUFFER-FIELD(3):BUFFER-VALUE.
    ELSE cstring = cstring + ", " + hbuftemptable:BUFFER-FIELD(3):BUFFER-VALUE.
    i = hbuftemptable:BUFFER-FIELD(2):BUFFER-VALUE.
    hquery:GET-NEXT(NO-LOCK).
END.
hquery:QUERY-CLOSE.
DELETE OBJECT hquery.
 
cstring = LEFT-TRIM(cstring,CHR(1)).
MESSAGE ENTRY(1,cstring,CHR(1)) SKIP
        ENTRY(2,cstring,CHR(1)) SKIP
        ENTRY(3,cstring,CHR(1)) VIEW-AS ALERT-BOX.

By the way, if you're using really large volumes of data, using a temp-table to store the records (now delimited by CHR(1)) would be a nicer solution.

 

[palthe]

Here is probably the simplest way of doing it, though depending on volume of data it could be quite slow:

output to "<output file name>".
FOR EACH <table-name> BREAK BY <table-name>.team BY tt-<table-name>.name:

IF FIRST-OF(<table-name>.team) THEN
PUT <table-name>.team " " <table-name>.name .
ELSE
PUT "," <table-name>.name.
IF LAST-OF(<table-name>.team) THEN
PUT SKIP.


END.

That is, by far, the simplest way to do it ! The FIRST-OF function, however, can not be used in dynamic querying. So if you're going to do things dynamically, the above is not an option. For static querying the above is a very good solution!

 

[Cecil Snodgrass]

output to <file-name>.

for each <table-name>:
export <table-name>.
end.

output close.

/* the default delimeter for the export statement is "," */

 

[TomBascom]

/* the default delimeter for the export statement is "," */

Err, ummm...

EXPORT [ STREAM stream | STREAM-HANDLE handle ] [ DELIMITER character ] { expression ... | record [ EXCEPT field ... ] } [ NO-LOBS ]

...

DELIMITER character

The character to use as a delimiter between field values. The character parameter must be a quoted single character. The default is a space character.

 

[Cecil Snodgrass]

Opps, Sorry, the default delimiter is a space.

export <table-name> delimiter ",".

 

[palthe]

Opps, Sorry, the default delimiter is a space.

export <table-name> delimiter ",".

But, correct me if I'm wrong, this is not an answer to the question, or am I missing something?

The TS wants to group data based on column values, not just export the records in a comma delimited way.