Db2

 View Only
Expand all | Collapse all

Roll up distinct values of a column into a single field.

  • 1.  Roll up distinct values of a column into a single field.

    Posted Fri July 08, 2022 11:38 PM

    Hello Forum,

    I have column which I want to roll up the distinct values into a single (new) field to output on my report.

    I have ocean shipping data which is key by a vessel / voyage.

    There are various port calls (e.g. LAX, OAK and SEA)

    The rows look at follows -

    Vessel/Voyage Port of Discharge

    Voyage-001 LAX

    Voyage-001 OAK

    Voyage-001 SEA

    I want to create a summary output

    Voyage-001 LAX,OAK,SEA Total Revenue

    The Port of Discharge data is what I am looking to roll up, within vessel/voyage.

    Any thoughts?

    Thanks in advance,

    Armando






    #Db2
    #Support
    #SupportMigration


  • 2.  RE: Roll up distinct values of a column into a single field.

    Posted Mon July 11, 2022 03:58 PM

    Hi,

    You can accomplish this in SQL using the following statement:

    SELECT voyageID, LISTAGG(port, ', ') WITHIN GROUP (ORDER BY port) AS Ports, SUM(revenue) FROM shipping_data GROUP BY voyageID

    You can create a view over the results, test, then in Web Query, create a synonym over this view and use it to create a report.

    Additional information can be found here. Please reach out to us a QU2






    #Db2
    #Support
    #SupportMigration