Geeks on Campus

Another amazing bgsu blog

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 revisit this post quite often to copy the syntax above. Here is another example that I just used in a report. I’m posting this mostly for my own future reference, but maybe someone else will get some use out of it.

Expression:
LISTAGG(CONCAT(C.DESCRSHORT, CONCAT(‘ (‘,CONCAT(DECODE(B.AWARD_STATUS,’C’,’Cancelled’,’D’,’Declined’,’Accepted’), CONCAT(‘) ‘,B.DISBURSED_AMOUNT)))), ‘ – ‘) WITHIN GROUP (ORDER BY B.EMPLID, B.ITEM_TYPE)

Output:
SubLn (Accepted) 1732 – UnsubLn (Accepted) 990 – UnsubLn8 (Accepted) 1979 – Plus Ln (Cancelled) 0

Leave a Reply

Your email address will not be published. Required fields are marked *