Teradata XMLAGG function with examples

XMLAGG function:

The XMLAGG function is used to perform an aggregate of multiple rows. This can used for multiple purpose such as constructing XML value or merging rows string values into a single row value.

Syntax of XMLAGG function:


The XMLAGG function concatenates values of a table column from multiple rows into a single string. The ORDER BY clause is applied to the query result set after all aggregate fields are evaluated, ORDER BY cannot directly affect the sequence of values within this string.

Under certain circumstances, XMLAGG results may appear in sequential order, but this ordering should not be relied upon. The values listed within a given aggregate result value cannot be explicitly ordered.

Example for create table and its values inside the table:

Source Data to perform XMLAGG operation

Example for XMLAGG function:

Here the XMLAGG function is aggregates the value of amount that is concatenated with comma(,) . The resulting value are ordered according to the ORDER CLAUSE.In this example,the result set is ordered ascending based on Transaction date.

Output of XMLAGG function

Output of XMLAGG function

Recommended Articles