Hi,

Anyone have a good way in SQL (DBISAM V3.3) to concatenate a field value from multiple records into one record?  So, for example, I have:

Table1 Record A, Fld1 value = 'Alex'
Table1 Record B, Fld1 value = 'John'
Table1 Record C, Fild1 value = 'Paul'

I want to update Table2 Fld 1 to contain 'Alex; John; Paul'   

a concatenation of all the records.

Help as always greatly appreciated.

Alex

Alex


The only way I can think of is to use a script, probably creating a memory table for each of the items wanted and then use those to update Table2, totally untested but something like

SELECT Fld1 INTO Memory/R1 FROM Table1 WHERE xxxxx;
SELECT Fld1 INTO Memory/R2 FROM Table1 WHERE xxxxx;
SELECT Fld1 INTO Memory/R3 FROM Table1 WHERE xxxxx;
UPDATE Table2 SET Fld1 = Memory/R1.Fld1+';'Memory.R2.Fld1+';'+Memory/R2.Fld1;
DROP Memory/R1;
DROP Memory/R2;
DROP Memory/R3;


Its a long time since I used DBISAM so the syntax is probably wrong

Roy Lambert Roy Lambert [Team Elevate]

Hmm, thanks Roy, the problem is I don't know how many matching records there will be.  I might just have to do the concatenation in ReportBuilder, I always prefer however to get as much done as possible in SQL.

Thanks again.

Alex


If you're using one of the later versions of DBISAM you do have the option of creating a user defined function. Have a look in the pdf manual - I think under customising the engine.


Roy Lambert [Team Elevate]