Collect() in oracle equivalent in teradata?

General

Collect() in oracle equivalent in teradata?

Base Data:

DEPTNO ENAME
---------- ----------
20 SMITH
30 ALLEN
30 WARD
20 JONES
30 MARTIN
30 BLAKE
10 CLARK
20 SCOTT
10 KING
30 TURNER
20 ADAMS
30 JAMES
20 FORD
10 MILLER

Desired Output:

DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

how to get desired output in teradata using sub query or any other option?

3 REPLIES

Re: Collect() in oracle equivalent in teradata?

You can get the desired output using WITH RECURSIVE of Teradata.

Re: Collect() in oracle equivalent in teradata?

I am new to Teradata, Please explain with example.

Re: Collect() in oracle equivalent in teradata?

First you need to create ranking of your base data and store into one table. Like below.

INSERT INTO yourdb.yourtb2
SELECT
DEPTNO
,ENAME
,RANK() OVER (PARTITION BY DEPTNO ORDER BY ENAME)  RNK
FROM yourdb.yourtb

After that, you need run recursive query like below to achive the desired output -

WITH RECURSIVE base (c_rnk, c_dept, c_list)
AS
(
SELECT
rnk c_rnk,
DEPTNO c_dept,
CAST(TRIM(ENAME) AS VARCHAR(5000)) c_list
FROM yourdb.yourtb2
WHERE rnk = 1

UNION ALL

SELECT
rnk c_rnk,
DEPTNO c_dept,
b.c_list || ',' || CAST(TRIM(c.ENAME) AS VARCHAR(5000)) c_list
FROM yourdb.yourtb2 c
INNER JOIN base b
ON c.DEPTNO = b.c_dept
AND  b.c_rnk + 1 = c.rnk

)

SELECT  c_dept, c_list FROM base
WHERE (c_dept, c_rnk) IN (SELECT c_dept, MAX(c_rnk) FROM base GROUP BY 1)
;