query help to concatonate fields

Question

query help to concatonate fields

Jeff-132889

Ten Centuries

Points: 1112
More actions
December 7, 2007 at 6:51 pm

#201538

Thanks all but I solved this myself ..........
Jeff
I have the following table data that I need to query (ss2k) to have the final output as:
79948899, part 1 of note text part 2 of note text
79948884, part 1 of note text
79939891, part 1 of note text part 2 of note text part 3 of note text
etc.....
Original table:
recordseq, noteseq, notetext
79948899, 1,part 1 of note text
79948899, 2,part 2 of note text
79948884, 1,part 1 of note text
79939891, 1,part 1 of note text
79939891, 3,part 2 of note text
79939891, 4,part 3 of note text
79939684, 1,part 1 of note text
79939684, 2,part 2 of note text
79939684, 3,part 3 of note text
tia, jeff

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Two way street here, Jeff... how about posting your solution? Two reasons... like I said, "two way street" and the other is these types of problems typically lead to performance problems in the face of scalability... you may or may not have the best solution and we'd still like to help make sure you have the best solution possible. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff-132889 Ten Centuries Points: 1112 More actions · Answer 2

Sure thing I'm more than happy to post the solution. I hadn't posted this as the bulk of the solution is non SQL related, specifically as you mentioned, to prevent any performance or scalability issues down the line.

The environment that this all takes place in is (starting from the mssql layer): mssql -> debian_linux -> free_tds -> unix_odbc-> php5

As shown earlier the solution needs to output several rows that are related to a single note into one record. The original record is composed of one or more rows in the database. The reason for this is that the SS2k table is a batch import of the original notes are that are in an RMS file system and are limited to 50 characters per note. The 'rows' in the file system are created via a VMS application was developed in the late 80's. Instead of modifying the length of the 'note' field the developers added additional sequenced records for the same note to accomodate the longer fields.

My solution is to do a straight query of the table with a couple sorts to come up with a recordset that populates a php array. The array loops through each record and contcatonates the results until the records sequence from the array changes and then starts a new record. If I had done the logic at the SS2K level it would have been redundant

since printing the records at the PHP level requires looping thru the array anyway.

Hope that some part of this solution proves helpful to someone else!

Jeff