Teradata XMLAGG function with examples
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:
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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 ); |
Example for XMLAGG function:
|
1 2 3 4 5 |
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.
|
1 |
SELECT * FROM Banking_DB.customer55; |
Output of XMLAGG function
Recommended Articles
- Pivot function in Teradata with examples
- How to use Qualify Row_number in Teradata?
- Concatenation operator and Concat function in Teradata with examples
