asked on

How to concatenate strings from fields of different records in a Paradox database

Hi, can anyone help me on this:

I have a paradox database that f.i. contains a field ''Unit" ( string)

What I want to do is to concatenate the value of this field , I'l get more specific with an example

record 1: Unit = "Mario"
record 2: Unit = "Peter"
record 3: Unit = "Marc"

Result --> "Mario / Peter / Marc" --> string

Can this be done by using SQL ?

thx
Marioway

ASKER CERTIFIED SOLUTION

kretzschmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pierre Cornelius

I doubt you can do it with the way your database is set up. You can concatenate strings from different fields in a single record (e.g. SELECT Field1||Field2 FROM SomeTable) but as far as I know, you can't do it on various records.

If you only want it for a few records, there is a bit of a long winded approach to your problem. Assuming your unique record identifier is IDNumber, and you want to concatenate the records for IDNumbers "123", "456" and "789" do the following:
1.) save the following as a text file in your database directory:
SELECT IDNumber, Unit FROM YourTable WHERE IDNumber = 123 /*or "123" if it is a text field*/
-> Call it Source1.sql for purposes of this example

2.) save the following as a text file in your database directory:
SELECT IDNumber, Unit FROM YourTable WHERE IDNumber = 456
-> Call it Source2.sql for purposes of this example

3.) save the following as a text file in your database directory:
SELECT Unit FROM YourTable WHERE IDNumber = 789
-> Call it Source3.sql for purposes of this example

4.) Execute the following query:
SELECT s1.Unit||"/"||s2.Unit||"/"||s3.Unit
FROM "c:\YourDBPath\source1.sql" s1,
"c:\YourDBPath\source2.sql" s2,
"c:\YourDBPath\source3.sql" s3

E.G. Using the DBDemos table Industry I have done this using the following:
- SELECT * FROM Industry WHERE IND_CODE = 3579 saved as Source1.sql
in "C:\Program Files\Common Files\Borland Shared\Data\"
- SELECT * FROM Industry WHERE IND_CODE = 3710 saved as Source2.sql
in "C:\Program Files\Common Files\Borland Shared\Data\"
- SELECT * FROM Industry WHERE IND_CODE = 7000 saved as Source3.sql
in "C:\Program Files\Common Files\Borland Shared\Data\"

- Execute this query:
SELECT s1.IND_NAME||"/"||s2.IND_NAME||"/"||s3.IND_NAME AS UnitCombined
FROM
"C:\Program Files\Common Files\Borland Shared\Data\Source1.sql" s1,
"C:\Program Files\Common Files\Borland Shared\Data\Source2.sql" s2,
"C:\Program Files\Common Files\Borland Shared\Data\Source3.sql" s3
- Results:
Softw/Auto/Hotel

As I said, this is a rather longwinded approach. Depending on your needs and on how many records you need to concatenate, you might find something like the following more useful:

function ConcatenateFields(DataSet: TDataset; ConcatField: string): string;
begin
result:= '';
DataSet.First;
While not DataSet.Eof do
begin
result:= result+DataSet.FieldByName(ConcatField).AsString+'/';
Dataset.Next;
end;
if result <> '' then result:= copy(result,1,Length(result)-1); //get rid of the last slash
end;

Hope this helps.

Kind regards
Pierre Cornelius

marioway

ASKER

Hello, Kretchzmar, I changed th code a bit, now it looks like this and it works fine

procedure TForm1.Button1Click(Sender: TObject);
var i: integer ;
i1, i2, i3 : string;
begin
query1.Close; (Select Eenheidsmaat from artikel.db)
query1.Prepare;
Query1.Open;
i1:= Query1Eenheidsmaat.Value; (or Unit in the question)
for i:= 0 to query1.RecordCount - 1 do
begin
i3:= i1;
query1.Next;
i2:= '/' + query1Eenheidsmaat.Value;
i1:= i3 + i2;
end;
Label1.Caption:= i3;

thx alot

Marioway
end;