Use LISTAGG as a query expression, to string several rows into a single row, in a single column.
Syntax
The syntax for the LISTAGG function in Oracle/PLSQL is:
LISTAGG (measure_column [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]
Parameters or Arguments
- measure_column
- The column or expression whose values you wish to concatenate together in the result set. Null values in the measure_column are ignored.
- delimiter
- Optional. It is the delimiter to use when separating the measure_column values when outputting the results.
- order_by_clause
- It determines the order that the concatenated values (ie: measure_column) are returned.
Returns
The LISTAGG function returns a string value.
Example
The LISTAGG function can be used in Oracle/PLSQL.
Since this is a more complicated function to understand, let's look at an example that includes data to demonstrate what the function outputs.
If you had a products table with the following data:
| product_id | product_name |
|---|---|
| 1001 | Bananas |
| 1002 | Apples |
| 1003 | Pears |
| 1004 | Oranges |
And then you executed a SELECT statement using the LISTAGG function:
SELECT LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) "Product_Listing" FROM products;
You would get the following results:
| Product_Listing |
|---|
| Apples, Bananas, Oranges, Pears |
In this example, the results of the LISTAGG function are output in a single field with the values comma delimited.
You can change the ORDER BY clause to use the DESC keyword and change the SELECT statement as follows:
SELECT LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name DESC) "Product_Listing" FROM products;
This would give the following results:
| Product_Listing |
|---|
| Pears, Oranges, Bananas, Apples |
You could change the delimiter from a comma to a semi-colon as follows:
SELECT LISTAGG(product_name, '; ') WITHIN GROUP (ORDER BY product_name DESC) "Product_Listing" FROM products;
This would change your results as follows:
| Product_Listing |
|---|
| Pears; Oranges; Bananas; Apples |
In Query Expression, you can add this LISTAGG function.
The below is an example for the Vendor Address phone number table in PeopleSoft FSCM. The intended desire is to display each SETID and VENDOR_ID on its own row, but only once per combination. Then we want a third field to put all phone numbers into a single column. If a vendor has 1 or 20 phone numbers, we just want one row of data, but list all phone numbers in the third field in our results.
Steps to produce this in PEOPLESOFT FSCM
- Create a new Query in Query Manager.
- Insert record VENDOR_ADDR_PHN.
- The Effective Dated logic will be automatically added. This is fine.
- Select the following fields to be displayed: SETID and VENDOR_ID.
- Use the checkboxes on the Query tab.
- Go to the Expressions tab.
- Create a new Expression that has the following (screenshot found later in this post):
- Expression Type: Long Character
- Aggregate Function: Turn this checkbox on.
- Expression Text: LISTAGG(A.PHONE, ‘ , ‘) WITHIN GROUP (ORDER BY A.PHONE)
- (If you are using this trick with other tables already added, be sure to use the proper alias and field name. Our example assumes the field PHONE is from record alias “A”.)
- Click OK, to save the new expression.
- Choose “Use as Field“, for our new expression.
- This adds our new expression to the Fields tab.
- Modify any heading text on the Fields tab.
- Run to view results.
- You should find that each SETID and VENDOR_ID have their own row, with the third field (Phone) having 1 or more phone numbers, separated by commas.
Bonus
You can also try out this Expression Text. It adds the Phone Type.
LISTAGG(A.PHONE_TYPE %CONCAT ':' %CONCAT A.PHONE, ' , ') WITHIN GROUP (ORDER BY A.PHONE_TYPE %CONCAT ':' %CONCAT A.PHONE)
Results
