Need help STRING_SPLIT

• abdalah.mehdoini

  Ten Centuries

  Points: 1015

  More actions

  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

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  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
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

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

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  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)