Sunday, September 6, 2015

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;


4 comments:

  1. OH MY GOD this would have saved me so much time over the years, especially for huge records like PS_JOB. I can't even count the amount of times I had to paste into a text editor and do delete, end, paste ', ' delete, end, paste ', ' (Until I got UltraEdit and could at least record a macro but still.)

    At least half my clients were on Oracle and this would have been so good to know. With this and MINUS that I learned by chance also years ago, I really should just sit down and read the list of Oracle SQL functions someday. God know what wheels I reinvent every day.

    ReplyDelete
    Replies
    1. @Jason Gill - Indeed. I too wish I knew about this before. Although, I believe it is relatively new (released in 11g).

      I had a similar approach in the past. I used Programmer's File Editor (PFE) which allows us to look for 'Escape Sequences' in the find and replace functionality. So, I used to find "\n" and replace it with ",\n" or simply ", " depending on my requirement.

      I am sure LISTAGG can be used for many other such use cases where we want to transpose rows of data into a column!

      Delete
  2. Hi Sasank, what about sub-record fields in table

    ReplyDelete
  3. I am not sure. I have not tested this SQL for sub-record fields. You will need to extend it if you want to include sub-record fields.

    ReplyDelete