Post reply

query help to concatonate fields

  • 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

  • December 8, 2007 at 9:59 am

    #758298

    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)
    Intro to Tally Tables and Functions

  • December 8, 2007 at 11:04 am

    #758306

    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

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

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