Oracle SQL: String Aggregation to Convert Rows of Data into a Single Column - LISTAGG Function

I recently learnt about this cool Oracle SQL function called LISTAGG. I am sure some of you must have come across this function already. I found it very useful for a lot of cases to perform string aggregation to convert rows of data into a single column.

Here is an example to aggregate multiple rows of fields for a particular record (PSRECFIELD) into a single column.

SQL which returns multiple FIELDNAME rows per RECNAME on PSRECFIELD:

select RECNAME, FIELDNAME from PSRECFIELD where recname = 'PSOPRDEFN' ORDER BY FIELDNUM;

 
Now let us convert these rows of fields into a single column using string aggregation (LISTAGG).

SQL using LISTAGG which returns one row with all fields per RECNAME on PSRECFIELD:


select RECNAME, LISTAGG(FIELDNAME, ', ') WITHIN GROUP (ORDER BY FIELDNUM) "Field_Listing" from PSRECFIELD where recname = 'PSOPRDEFN' group by RECNAME;

We can see that the list of fields were all aggregated and separated by a comma. This function is very useful for several reasons. I use the above SQL specifically for selecting all columns from a particular table/view instead of having to type them out by hand.

E.g.: I can copy the results of Field_Listing column and use it in my select statement.

select OPRID, VERSION, OPRDEFNDESC, EMPLID, EMAILID, OPRCLASS, ROWSECCLASS, OPERPSWD, OPERPSWDSALT, ENCRYPTED, SYMBOLICID, LANGUAGE_CD, MULTILANG, CURRENCY_CD, LASTPSWDCHANGE, ACCTLOCK, PRCSPRFLCLS, DEFAULTNAVHP, FAILEDLOGINS, EXPENT, OPRTYPE, USERIDALIAS, LASTSIGNONDTTM, LASTUPDDTTM, LASTUPDOPRID, PTALLOWSWITCHUSER
from PSOPRDEFN;

Another good example:

SQL using LISTAGG to return list of key fields (comma separated) for particular record definition in a single row.


SELECT A.RECNAME, Listagg(A.FIELDNAME, ', ') within GROUP (ORDER BY A.FIELDNUM) "RECORD_KEY_FIELDS" FROM   PSRECFIELD A WHERE  A.RECNAME = 'NAMES' AND Bitand(A.USEEDIT, 1) > 0 GROUP  BY A.RECNAME;