Teradata XMLAGG function with examples

Posted on by

Contents

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:

XMLAGG(column_to_merge , ORDER BY sort_column_name [DESC|ASC])

XMLAGG and ORDER BY :

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:

CREATE MULTISET TABLE Banking_DB.CUSTOMER44 ,FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
MAP = TD_MAP1
(
Cust_Id INTEGER,
Txn_dt DATE FORMAT 'YYYY/MM/DD',
Amount INTEGER
);
Source Data to perform XMLAGG operation

Example for XMLAGG function:

INSERT INTO Banking_DB.customer55
SELECT Cust_id, MAX(Txn_dt),
XMLAGG(Amount || ',' order by Txn_dt)
FROM Banking_DB.CUSTOMER44
GROUP BY 1;

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. 

SELECT * FROM  Banking_DB.customer55;

Output of XMLAGG function

Output of XMLAGG function

Recommended Articles