• 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
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    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

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25

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 © 2025 IT Jungle