SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
04-20-2008, 06:39 AM
| ||||
| ||||
 Re: Informix 4gl Query How do you acomplish this in SQL Server? Chucho! -----Original Message----- From: gary.cobden@nhs.net (Gary Cobden) To: informix-list@iiug.org Date: 10 Sep 2004 07:57:45 -0700 Subject: Informix 4gl Query 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 Jean Sagi jeansagi@myrealbox.com jeansagi@yahoo.com sending to informix-list      |
|
04-20-2008, 06:39 AM
| ||||
| ||||
| Re: Informix 4gl Query "Jean Sagi" <jeansagi@myrealbox.com> wrote in message news:cht6tk$c7k$1@news.xmission.com... > How do you acomplish this in SQL Server? You asked for it, Jean :-) CREATE PROCEDURE consolidate_text AS BEGIN DECLARE @last_id INTEGER DECLARE @this_id INTEGER DECLARE @line_no INTEGER DECLARE @old_text VARCHAR(255) DECLARE @new_text VARCHAR(255) SET @last_id = NULL SET @new_text = NULL SET NOCOUNT ON DECLARE get_data CURSOR LOCAL FORWARD_ONLY FOR SELECT customer_id, text_line_number, LTRIM(RTRIM(text_info)) FROM old_table WHERE LEN(LTRIM(RTRIM(text_info))) > 0 ORDER BY 1, 2 OPEN get_data WHILE 1 = 1 BEGIN FETCH NEXT FROM get_data INTO @this_id, @line_no, @old_text IF @@FETCH_STATUS <> 0 BREAK IF @this_id <> @last_id BEGIN INSERT INTO new_table VALUES (@last_id, @new_text) SET @new_text = NULL END IF @new_text IS NULL SET @new_text = @old_text ELSE SET @new_text = @new_text + ' ' + @old_text SET @last_id = @this_id END CLOSE get_data IF @last_id IS NOT NULL INSERT INTO new_table VALUES (@last_id, @new_text) END I think we can all agree that Informix SPL is neater (see below). -- Regards, Doug Lawry www.douglawry.webhop.org > "Doug Lawry" <lawry@nildram.co.uk> wrote in message > news:chsknb$a9j$1@nntp0.reith.bbc.co.uk... > > > 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 |
Linear Mode
