01
Fri, Nov
2 New Articles

New Solution for Combining Multiple Row Values into a Single Row with SQL

SQL
Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

The LISTAGG function makes combining row values easier.

Several years have passed since I wrote articles on how to use the Db2 for i Connect By and Recursive Common Table Expression features to combine multiple row values into a single row. The following table represents the type of report that could be generated using the SQL detailed in those articles.

Make

Models

Chevy

Malibu, Tahoe

Ford

Focus, Fusion, Taurus

Honda

Odyssey

Both of these features depend on recursive SQL processing, which results in SQL statements that are difficult to understand. I believe that you will agree with this assessment if you go back and review the SQL examples in my earlier articles.

Luckily, the SQL standard has evolved since that time. Starting with the Db2 for i 7.2 release, the ability to combine multiple row values into a single row is much easier with the LISTAGG function. This simple SELECT statement can generate the make and model report shared earlier with the LISTAGG function.

SELECT make,

             LISTAGG(model, ', ' ) WITHIN GROUP (ORDER BY model) Models

FROM inventory

GROUP BY make

The LISTAGG function aggregates a set of string values for the current SQL group into a single string value. In this SELECT statement, the specified SQL group is defined by the values in the Make column. The LISTAGG function is executed for each unique value in the column. Based on this sample data for the inventory table, the LISTAGG function would be performed for three different groups: Ford, Chevy, Honda.

Make

Model

Ford

Fusion

Chevy

Tahoe

Honda

Odyssey

Ford

Taurus

Ford

Focus

Chevy

Malibu

The first argument on the LISTAGG function specifies that the model column will supply the set of string values that will be combined into a single string value. The second value on the LISTAGG function is the separator argument. In this instance, a separator value of ', ' has been specified so that string will be placed between each pair of model values returned for the current make. The separator argument is optional. If that parameter were dropped from this example, the Model value for Chevy would be 'MalibuTahoe'. The WITHIN GROUP clause is also optional. This clause is used to specify the order in which the individual string values are combined into a single string. If the clause is not specified, then Db2 can aggregate the string values in any order. In this example, the value will be sorted by the Model column value.

The LISTAGG function also supports additional options that can be useful, depending on the amount and type of data values being processed. The following query uses the LISTAGG function on the SYSCOLUMNS catalog view to generate the list of data types and column names using that type in the QSYS2 schema.

SELECT data_type,

   LISTAGG(column_name, ', ')

WITHIN GROUP (ORDER BY column_name) "Column Name List"

FROM qsys2.syscolumns WHERE table_schema='QSYS2'

GROUP BY data_type

Figure 1 contains the output from the query. Notice in the Column Name List value for the CLOB data type that the DEBUG_DATA column name is repeated three times. The LISTAGG function includes duplicate values by default, but there is an option to change that behavior. It should also be obvious in this query output that the Column Name List is missing for the highlighted data type values (e.g., CHAR). That’s because a mapping error occurred due to the maximum size limit for the LISTAGG output string being exceeded. When the input string argument to the LISTAGG function is a VARCHAR column like column_name, the output size is 4000 or size of the input argument. In this example, column_name is a VARCHAR(128), so the maximum output size is 4000 bytes. Since there are so many columns in the QSYS2 schema defined with the highlighted data type values, a mapping error is returned for the LISTAGG function.

New Solution for Combining Multiple Row Values into a Single Row with SQL - Figure 1 

Figure 1: LISTAGG Query Output from SYSCOLUMNS

Below you’ll see that the previous query has been updated with new options to address the duplicate data and the mapping errors.

SELECT data_type,

   LISTAGG(DISTINCT column_name, ', ' ON OVERFLOW TRUNCATE WITH COUNT)

       WITHIN GROUP (ORDER BY column_name) "Column Name List"

FROM qsys2.syscolumns WHERE table_schema='QSYS2'

GROUP BY data_type

In Figure 2, the duplicate values in the LISTAGG output have been eliminated by simply adding the DISTINCT clause to the column_name input value. The data mapping errors also are no longer part of the query output thanks to the OVERFLOW clause. The default clause is ON OVERFLOW ERROR, which is the behavior shown in Figure 1.

The ON OVERFLOW TRUNCATE WITH COUNT option avoids the mapping error by truncating the string and adding a count of number of input values that were not added to the input string. This count of the truncated input values is shown in Figure 2. Another way to avoid the mapping error is to CAST the input column size to a bigger value (e.g., LISTAGG(DISTINCT CAST(column_name as VARCHAR(6000))…) to increase the LISTAGG maximum output size.

New Solution for Combining Multiple Row Values into a Single Row with SQL - Figure 2 

Figure 2: Query Output with Truncated Count Values

Surely, you now see how the LISTAGG function enables you to more easily combine values from multiple rows than the previous SQL support.

Kent Milligan
Kent Milligan is a Senior Db2 for i Consultant in the IBM Lab Services Power Systems Delivery Practice.  Kent has over 25 years of experience as a Db2 for IBM i consultant and developer working out of the IBM Rochester lab. Prior to re-joining the DB2 for i Lab Services practice in 2020, Kent spent 5 years working on healthcare solutions powered by IBM Watson technologies. Kent is a sought-after speaker and author on Db2 for i & SQL topics.
BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: