Search code examples
sqldatabasedb2peoplesoft

Straddle Query Results Instead of Stacking Duplicate Values

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!

Solution

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