All Forums General
rohithrsls 2 posts Joined 12/12
26 Dec 2012
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?
 

rsls
KS42982 137 posts Joined 12/12
26 Dec 2012

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

rohithrsls 2 posts Joined 12/12
27 Dec 2012

I am new to Teradata, Please explain with example.

rsls

KS42982 137 posts Joined 12/12
27 Dec 2012

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)
;

 

You must sign in to leave a comment.