Post reply

Need help STRING_SPLIT

  • December 12, 2020 at 3:16 pm

    #3819725

    hello

    How would you combine the results from two or more columns for the same unique customer #?

    Example Table:

    customer | message

    1               | some text

    1               | even more text

    2               | blah

    Return:

    1 | some text even more text

    2 | blah

    I'm working in Informix, but maybe if I can get an idea of how people would do it in SQL Server, I can find an Informix alternative

    thanks

  • December 12, 2020 at 5:08 pm

    #3819767

    In SQL Server 2017 and higher - you can use STRING_AGG...and Informix has LISTAGG: https://www.ibm.com/support/knowledgecenter/SSGNPV_2.0.0/com.ibm.db2.luw.sql.ref.doc/doc/r0058709.html

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 12, 2020 at 8:37 pm

    #3819834

    abdalah.mehdoini wrote:

    hello

    How would you combine the results from two or more columns for the same unique customer #? Example Table: customer | message 1               | some text 1               | even more text 2               | blah

    Return: 1 | some text even more text 2 | blah

    I'm working in Informix, but maybe if I can get an idea of how people would do it in SQL Server, I can find an Informix alternative

    thanks

    With the data you've provided, or rather with the data you've not provided, it's impossible to guarantee the correct order.  You need two elements.  You have the first element (a partition) based on the Customer ID.  In order to guarantee the correct order, you would need a second element/partition within custom to identify the correct order within the customer partition.  Your current data does not have that very necessary second element.

    A temporal column such as a row creation date, row modification date, or an identity column would probably suffice as the necessary second element.

     

    --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

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

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