Combine Multiple Rows and Fields
Posted by jeggent on June 28, 2018
TLDR;
Use LISTAGG(CONCAT(D.DESCR, CONCAT(‘ $’,C.OFFER_AMOUNT)), ‘ – ‘) WITHIN GROUP (ORDER BY B.EMPLID, C.PKG_SEQ_NBR)
To make this:
| Student | Term | Award | Amount |
|---|---|---|---|
| A. Person | Fall 18 | BG Scholarship | 1000 |
| A. Person | Fall 18 | Federal Grant | 2000 |
| A. Person | Fall 18 | Federal Loan | 3500 |
| B. Clever | Fall 18 | BG Scholarship | 1000 |
| B. Clever | Fall 18 | Outside Scholarship | 2500 |
Look like this:
| Student | Term | Awards |
|---|---|---|
| A. Person | Fall 18 | BG Scholarship $1000 – Federal Grant $2000 – Federal Loan $3500 |
| B. Clever | Fall 18 | BG Scholarship $1000 – Outside Scholarship $2500 |
The Details
The LISTAGG function can be used in an expression to combine multiple rows of data. The field(s) in the LISTAGG are grouped and then ordered by student. A more simple version would be:
LISTAGG(D.DESCR, ‘ – ‘) WITHIN GROUP (ORDER BY B.EMPLID)
This would give you a field with all of the awards separated by a space, dash, and space. “Scholarship – Grant – Loan”
You can combine the LISTAGG function with the CONCAT function to combine multiple fields (Description and Amount) and/or fields and strings (Amount and “$”).
You can also specify multiple fields in the ORDER BY to sort on more than one field. In the top example it is sorting by student and then within the student sorting the awards in the order they appear on the Assign Awards to a Student screen.
LISTAGG is (hopefully obviously) an aggregate function, so be sure to check the Aggregate Function checkbox on the “Edit Expression Properties” box and specify a length that will be enough to accommodate everything after combining.
I hope you find this useful. I think it’s really cool and makes for some much nicer looking output.
* Edit *
I’ve created a post just for LISTAGG Examples.