how to get enames with comma seperated values by deptwise
on emp table?
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
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 |
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 |
Answer / gj
SELECT deptno, LIST_AGG(ename) AS EMP_STRING
FROM emp
GROUP BY deptno;
Is This Answer Correct ? | 0 Yes | 0 No |
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 |
Answer / rasmita basantia
select ename ||' , '||deptno from emp order by deptno
Is This Answer Correct ? | 4 Yes | 7 No |
What is rank function in sql?
How do you create a db file?
Is it possible to access the current value in a session before accessing next value?
explain what is mysql? : Sql dba
Can we call dml statement in function?
What are the different datatypes available in PL/SQL?
What is difference between sql function and stored procedure?
Why is theta join required?
What is the difference between sum and count in sql?
Why do we use procedures?
What is pl/sql language case sensitive?
What is correlated sub-query?