Hi,
I need to concatenate multiple rows of data in to one single row.
eg:
emp no emp comments
123 i do not
123 know how to
123 do it

Assuming I have the above data in a table how do i convert it ito something like this:
emp no emp comments
123 i do not know how to do it

This final table should be kept in a hold file. Any suggestions???

Thanks.

Kalyan,
First sort the records by emp no.
Use the LAST command to compare the last previous record emp no and curent record emp no. If the emp no is same, then append the comments to a defined field.

hope it helps.

kj

Kalyan,

I had a similar situation where I got good replies in this forum back in last July. Search for my member number 515 and look for the topic "Create an addtional record on a HOLD file".

Hope this will help you!

--Thiru

then there's always
LIST COMMENT BY EMPNO
ON TABLE HOLD
END
TABLE FILE HOLD
SUM COMMENT ACROSS E01 BY EMPNO
END
...
E01 is the alias to field 1 in an interim hold file...and the LIST verb creates an integer ranking. if you have 3 comments per employee, you'll have a max value of 3 for the E01 field
for each employee number.
Depends on how nice you want your output to look.
This is just a quick and dirty way to slap it into one record.
Then you could define a field that concatenates
all the comments together, and removes multiple space, etc (see the CHARACTER functions in manual #4 Using FUnctions)

I used the LAST COMMAND by "kj".. Suited my purpose fine...
Thanks anyway for all your ideas...

There's still a problem in concatenating multiple rows for a single value.

KJ...your idea works, but i was able to concatenate just two rows of data using the LAST command. When there are more than two rows, only two adjacent rows are concatenated.

Susannah... SUM doesnt seem to work for character strings. Just one record is fetched when i use sum.

More light please....

SUM with Alphanumeric will grab the last record.

you can do a
SUM yourfield
BY yourfield NOPRINT

to accomplish what you need!

This is primitive but it works.
-* test data ********************************************
-*123i do not
-*123know how to
-*123do it
-*456this is how
-*456you do it
-*789it is
-*789a lot
-*789harder than
-*789it looks
-* mfd ***************************************************
*FILE=TESTDES1 ,SUFFIX=FIX
*SEGNAME=TESTDES1 ,SEGTYPE=S01
-*FIELDNAME =EMP_NO ,E01 ,A3 ,A3 ,$
-*FIELDNAME =EMP_COMMENTS ,E02 ,A11 ,A11 ,$
-**********************************************************
FILEDEF TESTDES1 DISK C:\IBI\APPS\SAMPLES\TESTDES1.FTM
-**********************************************************
TABLE FILE TESTDES1
SUM CNT.EMP_NO
BY EMP_NO
LIST EMP_COMMENTS
BY EMP_NO
ON TABLE HOLD
END
DEFINE FILE HOLD
COMMENTS1/A11 = IF LIST EQ 1 THEN E04;
COMMENTS2/A23 = IF LIST EQ 2 THEN COMMENTS1| ' ' | E04 ;
COMMENTS3/A35 = IF LIST EQ 3 THEN COMMENTS2| ' ' | E04 ;
COMMENTS4/A47 = IF LIST EQ 4 THEN COMMENTS3| ' ' | E04 ELSE ' ';
COMMENTS5/A59 = IF LIST EQ 5 THEN COMMENTS4| ' ' | E04 ELSE ' ';
NEW_COMMENTS/A100 = IF E02 EQ 1 THEN COMMENTS1 ELSE
IF E02 EQ 2 THEN COMMENTS2 ELSE
IF E02 EQ 3 THEN COMMENTS3 ELSE
IF E02 EQ 4 THEN COMMENTS4 ELSE
IF E02 EQ 5 THEN COMMENTS5 ELSE 'need more comment fields';
END
TABLE FILE HOLD
SUM NEW_COMMENTS
BY E01
END

Try this...

TABLE FILE TESTDES1
PRINT

COMPUTE
COMM1/A100 = IF EMP_NO EQ LAST EMP_NO THEN
(SUBSTR(100, (LAST COMM1), 1, 88, 88, 'A88')
|| (' ' | EMP_COMMENTS)) ELSE EMP_COMMENTS;

COMPUTE
CTR/I3 = IF EMP_NO EQ LAST EMP_NO
THEN CTR + 1 ELSE 1;
NOPRINT

BY EMP_NO
BY TOTAL HIGHEST 1 CTR NOPRINT
END

K. Piippo, very good I like that. That works perfect.