Whilst I have a MS SQL background, I need assistance with the
following please:-

I have a table with the following structure:-
customer_id; text_line_number; text_info

Each customer_id can have several records, each with a different
text_line_number

I need to consolidate all the records in a new table so that there is
one record for each customer_id and the text_info fields are
consolidated
eg:-
customer_id 7777;
text_line_number 1;
text_info abc;

customer_id 7777;
text_line_number 2;
text_info def;

becomes - in the consolidated record
customer_id 7777;
text_info abc def;

Any assistance would be appreciated

Thanks

Gary

Gary,

The following SQL (or similar 4GL) should do it:

CREATE PROCEDURE consolidate_text()

DEFINE last_id INTEGER;
DEFINE this_id INTEGER;
DEFINE line_no INTEGER;
DEFINE old_text VARCHAR(255);
DEFINE new_text VARCHAR(255);

LET last_id = NULL;
LET new_text = NULL;

FOREACH

SELECT customer_id, text_line_number, TRIM(text_info)
INTO this_id, line_no, old_text
FROM old_table
WHERE LENGTH(TRIM(text_info)) > 0
ORDER BY 1, 2

IF this_id != last_id THEN
INSERT INTO new_table VALUES (last_id, new_text);
LET new_text = NULL;
END IF

IF new_text IS NULL THEN
LET new_text = old_text;
ELSE
LET new_text = new_text || ' ' || old_text;
END IF

LET last_id = this_id;

END FOREACH

IF last_id IS NOT NULL THEN
INSERT INTO new_table VALUES (last_id, new_text);
END IF

END PROCEDURE;

EXECUTE PROCEDURE consolidate_text();

Use LVARCHAR if you are using version 9 and need more than 255 bytes.

--
Regards,
Doug Lawry
www.douglawry.webhop.org


"Gary Cobden" <gary.cobden@nhs.net> wrote in message
news:4a40e4ac.0409100657.1ca9a1d6@posting.google.c om...
> Whilst I have a MS SQL background, I need assistance with the
> following please:-
>
> I have a table with the following structure:-
> customer_id; text_line_number; text_info
>
> Each customer_id can have several records, each with a different
> text_line_number
>
> I need to consolidate all the records in a new table so that there is
> one record for each customer_id and the text_info fields are
> consolidated
> eg:-
> customer_id 7777;
> text_line_number 1;
> text_info abc;
>
> customer_id 7777;
> text_line_number 2;
> text_info def;
>
> becomes - in the consolidated record
> customer_id 7777;
> text_info abc def;
>
> Any assistance would be appreciated
>
> Thanks
>
> Gary