Can you combine several records into one field?

[buddyrich2]

I have a table where all the records are the same except for one field. Is there a way to combine that one (dissimilar) field into ONE separate field within the record? For example, if there are three records with all the fields the same except that field "fruit" contains apples. 2nd record contaings oranges, third contains bananas. I want one field that contains "apples, oranges, bananas", then delete the other records.

Can anyone tell me how I can programatically do this?

Thanks!

 

[white605]

Certinally this can be done but ? are you sure you want to destroy your data and only end up with one record?

*set up the table
CREATE TABLE test (field1 c(10),fruit c(10))
INSERT INTO test (field1,fruit) VALUES ("a","apples")
INSERT INTO test (field1,fruit) VALUES ("a","ornages")
INSERT INTO test (field1,fruit) VALUES ("a","bannanas")
*add a field to use
ALTER TABLE test ADD COLUMN field3 c(30)

Use test
goto top
copy to array arrayname while .not. eof()
goto top
repl field3 with TRIM(arrayname(1,2))+","+TRIM(arrayname(2,2))+","+TRIM(arrayname(3,2))
nRecno=RECNO()
DELETE FOR RECNO()>nRecno
pack
brow

 

[AndyKr]

It is not clear from your question whether this is a table in which there is only one column that is different for all records, or whether it is that each "set" of records has multiple possible values.
i.e. is the table like this:
A, B, C, D
A, B, C, E
A, B, C, F

or like this:
A, B, C, D
A, B, C, E
A, B, C, F
G, H, I, D
G, H, I, E
G, H, I, F
K, L, M, D
K, L, M, E
K, L, M, F

Either way, the best solution would probably be to pull the values out into a lookup table and store only the key (or keys) in each record. I would not normally advise combining the values because it then becomes difficult to differentiate them.

What exactly is the problem you are trying to solve here?


----
Andy Kramek
Visual FoxPro MVP

 

[buddyrich2]

That is a good idea, but the number of different records is unlimited (not just a defined number, like 3). Each "set" of records has multiple values.

Problem is when reporting, I don't want to end up with a commpletely separate record just because of that one field. I want that one field to show up but with comma separated values there.

Thanks.

 

[Mike Lewis]

Buddyrich,

It is still unclear what you actually want to do. At first, you said that "all the records are the same except for one field" (my emphasis). Later, you talked about "each set" of records with multiple values.

It's also unclear why you want to delete the original data. If your goal is to create a report, the best thing is to create a separate cursor that contains all the data for the report.

The following code might put you in the right direction, but without a clearer picture of what you are doing, it's hard to give any better advice:

SELECT DISTINCT Fruits FROM OriginalTablee ;
  INTO CURSOR csrFruits

CREATE CURSOR csrReport ( ;
 SPACE(254) AS Fruitlist, ;
 <other field definitions here> )

lcList = ""
SELECT csrFruits
SCAN
  lcList = ALLTRIM(Fruits) + ", "
ENDSCAN
IF RIGHT(lcList,2) = ", "
  lcList = LEFT(lcList, LEN(lcList)-2)
ENDIF

SELECT csrReport
REPLACE Fruitlist WITH lcList
* populate the other field in whatever
* way makes sense to you/

* You can now use csrReport to drive the report

This is completely off the top of my head, and not at all tested (not even carefully checked). I hope at least it will give you something to work with.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[buddyrich2]

Mike,

Good idea, but it doesn't work properly. lcList doesn't accumulate the values. The last one scanned ends up the only one in there.

Did you accidently leave something out?

 

[Mike Lewis]

Buddyrich,

This line:

lcList = LEFT(lcList, LEN(lcList)-2)

should have been like this:

lcList = lcLilst + LEFT(lcList, LEN(lcList)-2)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk