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

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

CREATE TABLE PUBLIC.orders (
	o_orderkey int,
	o_totalprice float);

INSERT INTO PUBLIC.orders
VALUES (1,500000);

INSERT INTO PUBLIC.orders
VALUES (2,100000);

INSERT INTO PUBLIC.orders
VALUES (3,600000);

INSERT INTO PUBLIC.orders
VALUES (4,700000);

XMLAGG transformation

Teradata

Query

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

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

Result

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

Known Issues

1. RETURNING clause is currently not supported.

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

Related EWIs

1. MSCEWI1073: Pending Functional Equivalence Review.