0

To keep this simple, my queries fields are: ORDER ID, ORDER AMOUNT, and ORDER TEXT.

My issue is that our company typically has multiple 'ORDER TEXT' fields, which is making my query return a second record (Stacked) with all duplicate data (ORDER ID, ORDER AMOUNT) except for the other 'ORDER TEXT', which is different text.

Is it possible to put the second 'ORDER TEXT' field next to the first 'ORDER TEXT' field (Straddled) OR even concatenate the two into one long 'ORDER TEXT' string in DB2?

Example of what is currently happening and what I want to happen

Assume you cannot create additional tables or manipulate the database in any way. As always, any help is greatly appreciated!

CRUTER
  • 866
  • 4
  • 12
  • possible duplicate of [What is SELF JOIN and when would you use it?](http://stackoverflow.com/questions/3362038/what-is-self-join-and-when-would-you-use-it) – mustaccio Jan 26 '15 at 18:05
  • Unlike mustaccio, Telkarion was on point with his suggestion of using the LISTAGG() function. – CRUTER Jan 26 '15 at 20:11
  • Not all versions of DB2 have the LISTAGG function. Since you didn't post yours, I offered the safest option that'd work anywhere. – mustaccio Jan 26 '15 at 20:13

1 Answers1

2

Try to use the aggregate function LISTAGG like this :

SELECT ORDER_ID, ORDER_AMOUNT,
     LISTAGG(ORDER_TEXT, ', ') WITHIN GROUP(ORDER BY ORDER_TEXT)
     AS orders
  FROM PS_PO_HDR
  GROUP BY ORDER_ID, ORDER_AMOUNT

Just replace the table name with the one you're using.

Details on the function available here (available since version 9.7.4)

Telkarion
  • 431
  • 5
  • 12