• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Combine Related Rows Using SQL

    February 12, 2018 Ted Holt

    A reader writes: “Hey, Ted. In our ERP system, certain business objects, such as sales orders and purchase orders, can have multiple comment records. Is it possible, using SQL, to combine all the comment records for an order into one long comment and retrieve it as a column in a result set?”

    I can relate to this. I can remember supporting an ERP system where not only the orders, but the order detail lines, could have such comments. End users depend heavily on such unstructured data to do their jobs. To answer your question, yes, it is possible and it isn’t difficult.

    Let’s begin with two sales order tables — headers and comments:

    create table slsordhd
      (OrderID  dec(5), OrderDate dec(7), CustID dec(7),
      primary key (OrderID));
    
    insert into slsordhd values
    (1, 1180130, 26424),
    (2, 1180131, 75179),
    (3, 1180201, 38493);  
    
    create table slsordcmt
      (OrderID dec(5), Sequence dec(3), comment char(20),
      primary key (OrderID, Sequence));
      
    insert into slsordcmt values
    (1, 1, 'Don''t ship without c'),
    (1, 2, 'hecking with Butch a'),
    (1, 3, 't x255.'), 
    (2, 1, 'Partial shipment is '),
    (2, 2, 'NOT acceptable.'),
    (3, 1, 'Low priority');
    

    Now we have three orders with comments. In the systems I’ve worked with, the comment fields are long, at least 80 bytes, but I made them only 20 bytes to keep the amount of data manageable.

    The function that combines the rows is LISTAGG. It combines a set of string values and it can separate them with a separator string of your choice. One common use I’ve seen for this function is to build a string of comma-separated values.

    Here’s the LISTAGG function to combine the comments for each order.

    select OrderID, 
           listagg(trim(comment))
              within group(order by OrderID, Sequence) as Cmt
      from slsordcmt
     group by OrderID
    
    OrderID Cmt
    1 Don’t ship without checking with Butch at x255.
    2 Partial shipment is NOT acceptable.
    3 Low priority

    The argument to LISTAGG is the comment field without leading or trailing blanks. Only you can decide if you should retain blanks or not.

    The last line tells the query to group rows on a common order ID. GROUP BY has been around for decades, so chances are you’re very familiar with it.

    The WITHIN GROUP may be new to you. This clause is part of LISTAGG, and its purpose is to tell the query engine how to sequence the rows within each group. In this case, I said that I want the comment rows sorted by order ID and sequence number.

    The order comment is probably not useful by itself. You probably want to combine it with other data. In the following query, I use a lateral subquery to combine it with fields from the order header file. LATERAL lets me run the second SELECT, which has the LISTAGG function, for each row of the first SELECT. The good thing about LATERAL is that the second SELECT is permitted to reference columns from the first SELECT. In this case, that’s h.OrderID. If you’re still not comfortable with the lateral subquery, sometimes called lateral correlation or lateral join, see the related stories at the end of this article.

    select h.OrderID, h.OrderDate, h.CustID, x.Cmt
      from slsordhd as h, 
     lateral (select OrderID, 
                     listagg(trim(comment))
                        within group(order by OrderID, Sequence) as Cmt
                from slsordcmt as c
               where c.OrderID = h.OrderID
               group by OrderID) as x
    where h.OrderDate <= 1180131
    
    OrderID Date Customer Cmt
    1 1180130 26424 Don’t ship without checking with Butch at x255.
    2 1180131 75179 Partial shipment is NOT acceptable.

    Of course, you may want even more tables and/or views in the join. Here’s the same query with the customer master table added in order to get the customer name. I was able to join the tables first and then tack the LATERAL on afterward.

    select h.OrderID, h.OrderDate, h.CustID, cu.CustName, x.Cmt
      from slsordhd as h
      left join cust as cu
        on h.CustID = cu.CustID, 
     lateral (select OrderID, 
                     listagg(trim(comment))
                        within group(order by OrderID, Sequence) as Cmt
                from slsordcmt as c
               where c.OrderID = h.OrderID
               group by OrderID) as x          
    where h.OrderDate <= 1180131
    
    OrderID Date CustID Customer Cmt
    1 1180130 26424 ACME Don’t ship without checking with Butch at x255.
    2 1180131 75179 SUPERIOR Partial shipment is NOT acceptable.

    That’s all there is to it. Play with it a bit and you’ll be a LISTAGG wizard in no time.

    RELATED STORIES

    More V5R3 SQL Enhancements

    Using Lateral Correlation To Define Expressions In DB2 For i

    LISTAGG

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, FHG, Four Hundred Guru, IBM i, SQL

    Sponsored by
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Bot Versus Bot NGS And LightEdge Provide New Entry To The Cloud

    4 thoughts on “Guru: Combine Related Rows Using SQL”

    • glenngundy says:
      February 12, 2018 at 9:58 am

      This is fantastic. Thank you Ted!!

      Reply
    • EWART DE SOUZA says:
      February 12, 2018 at 9:25 pm

      Hi Ted,

      The following statement does not work on my V7R1 system.

      select OrderID,
      listagg(trim(comment))
      within group(order by OrderID, Sequence) as Cmt
      from slsordcmt
      group by OrderID

      It says “Keyword GROUP not expected. Valid tokens: , FROM INTO.”

      Any idea why ?

      Thanks & Regards
      Ewart

      Reply
      • Ranjith K says:
        February 18, 2018 at 1:26 am

        SQL BiF LISTAGG added with v7. 3 R2 and v7. 2 R6…

        Reply
    • Tim Molter says:
      April 4, 2023 at 11:02 am

      I would add a good standard practice would be to include ON OVERFLOW TRUNCATE so the aggregated result string is truncated if the actual length of the result string exceeds the result length. See https://www.itjungle.com/2018/02/12/guru-combine-related-rows-using-sql/

      Reply

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 11

This Issue Sponsored By

  • Maxava
  • Profound Logic Software
  • Harkins & Associates
  • Manta Technologies
  • UCG TECHNOLOGIES

Table of Contents

  • IBM Preps Power9 “ZZ” Systems For Imminent Launch
  • NGS And LightEdge Provide New Entry To The Cloud
  • Guru: Combine Related Rows Using SQL
  • As I See It: Bot Versus Bot
  • Modernizing The IBM i Estate For Digital Transformation

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i Delivers Sizable Benefits, Forrester Consulting Reports
  • SBOMs Will Come to IBM i, Eventually
  • IBM i Backup Provider Storagepipe Snapped Up By Thrive
  • Four Hundred Monitor, June 7
  • IBM i PTF Guide, Volume 25, Number 23
  • Power10 Boosts NVM-Express Flash Performance
  • Fortra Completes Postmortem Of GoAnywhere Vulnerability
  • Guru: Binding Directory Entries
  • How Does Your Infrastructure Spending Stack Up To The World?
  • IBM i PTF Guide, Volume 25, Number 22

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2023 IT Jungle