how to get enames with comma seperated values by deptwise on emp table?

Categories >> Software >> Databases >> SQL PLSQL
Suggest New Category

how to get enames with comma seperated values by deptwise
on emp table?

Question Posted / mythili

8 Answers
9555 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

how to get enames with comma seperated values by deptwise on emp table?..

Answer / kavitha nedigunta

SELECT deptno, wm_concat(ename) AS EMP_STRING
FROM emp
GROUP BY deptno;

works in 10g

Is This Answer Correct ?

6 Yes

0 No

how to get enames with comma seperated values by deptwise on emp table?..

Answer / kavitha nedigunta

SELECT deptno ,
LTRIM(MAX( SYS_CONNECT_BY_PATH (
ename, ',')),',') EMP_STRING
FROM
(SELECT empno , ename, deptno,
row_number() OVER ( PARTITION BY deptno ORDER
BY rownum) rn
FROM EMP )
CONNECT BY deptno = PRIOR deptno
AND rn = PRIOR rn+1
START WITH rn =1
GROUP BY deptno
ORDER BY deptno;

Is This Answer Correct ?

5 Yes

0 No

how to get enames with comma seperated values by deptwise on emp table?..

Answer / amitabha mandal

COLUMN employees FORMAT A50

SELECT deptno, wm_concat(ename) AS employees
FROM emp
GROUP BY deptno;

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

3 rows selected.

check tyhis link :http://www.oracle-
base.com/articles/misc/StringAggregationTechniques.php

Is This Answer Correct ?

3 Yes

0 No

how to get enames with comma seperated values by deptwise on emp table?..

Answer / ramesh

select deptno,listagg(ename,',') within group(order by sal)
from emp group by deptno;

NOTE:IT IS WORKING ONLY 11G IF YOU ARE INSTALLING IN YOUR SYSTEM TRY IT GUYS

Is This Answer Correct ?

1 Yes

0 No

how to get enames with comma seperated values by deptwise on emp table?..

Answer / g

SELECT DEPT.DEPT_ID,LIST_AGG(E.EMP_NAME) FROM EMP E,DEPT D WHERE E.DEPT_ID=DEPT.DEPT_ID GROUP BY DEPT.DEPT_ID;

Is This Answer Correct ?

0 Yes

0 No

how to get enames with comma seperated values by deptwise on emp table?..

Answer / gj

SELECT deptno, LIST_AGG(ename) AS EMP_STRING
FROM emp
GROUP BY deptno;

Is This Answer Correct ?

0 Yes

0 No

how to get enames with comma seperated values by deptwise on emp table?..

Answer / pradeep

select deptno,cursor (select ename ,deptno from emp where
d.deptno = deptno ) from dept d

1* select deptno,cursor (select
SQL> /

DEPTNO CURSOR(SELECTENAME,D
---------- --------------------
10 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME DEPTNO
---------- ----------
CLARK 10
KING 10
MILLER 10

20 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME DEPTNO
---------- ----------
PORT NO 20
SMITH 20
JONES 20
SCOTT 20
ADAMS 20
FORD 20

6 rows selected.

30 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

ENAME DEPTNO
---------- ----------
ALLEN 30
WARD 30
MARTIN 30
BLAKE 30
TURNER 30
JAMES 30

6 rows selected.

40 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

no rows selected

Is This Answer Correct ?

0 Yes

1 No

how to get enames with comma seperated values by deptwise on emp table?..

Answer / rasmita basantia

select ename ||' , '||deptno from emp order by deptno

Is This Answer Correct ?

4 Yes

7 No

Post New Answer