XMLAGG

Translation specification for transforming the XMLAGG function to Snowflake LISTAGG

Description

Construct an XML value by performing an aggregation of multiple rows. For more information check XMLAGG.

XMLAGG (
  XML_value_expr
  [ ORDER BY order_by_spec [,...] ]
  [ RETURNING { CONTENT | SEQUENCE } ]
)

order_by_spec := sort_key [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Sample Source Patterns

Setup data

Teradata

Query

IN -> Teradata_01.sql

create table orders (
	o_orderkey int, 
	o_totalprice float);

insert into orders values (1,500000);
insert into orders values (2,100000);
insert into orders values (3,600000);
insert into orders values (4,700000);

Snowflake

Query

OUT -> Teradata_01.sql

CREATE TABLE orders (
	o_orderkey int,
	o_totalprice float)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO orders
VALUES (1,500000);

INSERT INTO orders
VALUES (2,100000);

INSERT INTO orders
VALUES (3,600000);

INSERT INTO orders
VALUES (4,700000);

XMLAGG transformation

Teradata

Query

IN -> Teradata_02.sql

select 
    xmlagg(o_orderkey order by o_totalprice desc) (varchar(10000))
from orders
where o_totalprice > 5;

Result

COLUMN1 |
--------+
4 3 1 2 |

Snowflake

Query

OUT -> Teradata_02.sql

SELECT
    LEFT(TO_VARCHAR(LISTAGG ( o_orderkey, ' ')
    WITHIN GROUP(
 order by o_totalprice DESC NULLS LAST)), 10000)
    from
    orders
    where o_totalprice > 5;

Result

COLUMN1 |
--------+
4 3 1 2 |

Known Issues

1. The RETURNING clause is currently not supported.

The user will be warned that the translation of the returning clause will be added in the future.