Equivalent of LISTAGG in teradata

General

Equivalent of LISTAGG in teradata

Hello Everyone!

I'm trying to aggregate all the field values of a particular group in one field.

For example, My input table looks like :-

FIELD1 GROUP1

A 1

A 2

B 1

B 2

C 1

C 3

3 null

and my output should look like :-

1 A,B,C

2 A,B

3 C


I can currently achieve this on Oracle by using the following function

SELECT GROUP1, LISTAGG(FIELD1, ',') WITHIN GROUP (ORDER BY FIELD1) AS GROUPED_FIELD
FROM <INPUT_TABLE>
GROUP BY GROUP1;

Is there a way i can do this in Teradata.

Any help would be appreciated!!

-Nihal

1 REPLY

Re: Equivalent of LISTAGG in teradata

Hi Nihal,

The solution of asked question can be solved by recursive method.I am writing the code.Check in your system.

WITH RECURSIVE MYREC(col1,col2,LVL)

 AS(

 SELECT col1, MIN(col2(integer(10))) AS col2,1

 FROM <table_name>

GROUP BY 1

 UNION ALL

 SELECT b.col1,trim(a.col2) || ',' || trim( b.col2), LVL+1

 FROM <table_name> a INNER JOIN MYREC b

 ON a.col1 = b.col1

 AND a.col2 > b.col2

 )

 SELECT col1, col2

 FROM MYREC

 QUALIFY RANK() OVER(PARTITION BY col1 ORDER BY col2 DESC) = 1

Put col1,col2 and table name as per your choice.

Regards,

Abhilash Kumar