No Offense William !! correct me if I am wrong I think this will do the job SQL> SELECT deptno 2 ,LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,',')) 3 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated 4 FROM ( SELECT deptno 5 , ename 6 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr 7 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev 8 FROM emp ) 9 GROUP BY deptno 10 CONNECT BY prev = prior curr 11 --AND 12 -- deptno = PRIOR deptno 13 START WITH curr = 1;
Hi Devang, None taken, and sorry for the four-month delay in replying.
The inner query on its own gives:
SELECT deptno , ename , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev FROM emp 2 3 4 5 ;
DEPTNO ENAME CURR PREV ---------- ---------- ---------- ---------- 10 CLARK 1 0 10 KING 2 1 10 MILLER 3 2 20 ADAMS 1 0 20 FORD 2 1 20 JONES 3 2 20 SCOTT 4 3 20 SMITH 5 4 30 ALLEN 1 0 30 BLAKE 2 1 30 JAMES 3 2 30 MARTIN 4 3 30 TURNER 5 4 30 WARD 6 5
14 rows selected.
Therefore deptno and curr form a composite key. I get the following with my version:
5 Comments:
Fantastic.
I like the user defined aggregate function best, but its great to see the analytic functions stretched so far :-)
This post has been removed by the author.
This post has been removed by the author.
No Offense William !!
correct me if I am wrong
I think this will do the job
SQL> SELECT deptno
2 ,LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
3 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
4 FROM ( SELECT deptno
5 , ename
6 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
7 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
8 FROM emp )
9 GROUP BY deptno
10 CONNECT BY prev = prior curr
11 --AND
12 -- deptno = PRIOR deptno
13 START WITH curr = 1;
Hi Devang,
None taken, and sorry for the four-month delay in replying.
The inner query on its own gives:
SELECT deptno
, ename
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
FROM emp 2 3 4 5 ;
DEPTNO ENAME CURR PREV
---------- ---------- ---------- ----------
10 CLARK 1 0
10 KING 2 1
10 MILLER 3 2
20 ADAMS 1 0
20 FORD 2 1
20 JONES 3 2
20 SCOTT 4 3
20 SMITH 5 4
30 ALLEN 1 0
30 BLAKE 2 1
30 JAMES 3 2
30 MARTIN 4 3
30 TURNER 5 4
30 WARD 6 5
14 rows selected.
Therefore deptno and curr form a composite key. I get the following with my version:
DEPTNO CONCATENATED
---------- --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
and the following without the join on prior deptno:
DEPTNO CONCATENATED
---------- --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 CLARK,KING,MILLER,SCOTT,SMITH
30 CLARK,KING,MILLER,SCOTT,TURNER,WARD
Post a Comment
Links to this post:
Create a Link
<< Index of articles