Alternate Options for Using LISTAGG Function

  • If we have a Requirement to Concatenate the list of values in to a Single Field than we can achieve it with LISTAGG Function.
  • As Listagg Function having Size limitation of 4000 Characters in listing the values we can achieve the same requirement by using STRAGG Function
  • Below are the Illustrations for both LISTAGG and STRAGG Function.

 

Example 1: LISTAGG Function.

Invoice_ID Invoice_Number
1001 Invoice1
1002 Invoice2
1003 Invoice3
1004 Invoice4

 

SQL Statement :

SELECT LISTAGG(Invoice_number, ‘, ‘) WITHIN GROUP (ORDER BY Invoice_number) “Invoice_number” FROM Invoice_table;

 

Output :

Invoice_number
Invoice1, Invoice2, Invoice3, Invoice4

 

Example 2 : STRAGG Function.

Invoice_ID Invoice_Number
1001 Invoice1
1002 Invoice2
1003 Invoice3
1004 Invoice4

 

SQL Statement :

SELECT sys.STRAGG(Invoice_number || ‘; ‘) “Invoice_number” FROM Invoice_table;

 

Output :

Invoice_number
Invoice1; Invoice2; Invoice3; Invoice4

 

Recent Posts