- 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
