SQL Question - Select clause within Select List

From: <logo_palanisamy_at_my-deja.com>
Date: 2000/06/28
Message-ID: <8jduf4$2hi$1@nnrp1.deja.com>#1/1

I have a SQL question for the table structure given below.

SQL> desc dept

 Name                                      Null?    Type


 ----------------------------------------- -------- --------------------

 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> desc emp
 Name                                      Null?    Type


 ----------------------------------------- -------- --------------------

 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> l
  1 select dname, ename from dept, emp
  2 where dept.deptno = emp.deptno
  3* order by dname

DNAME ENAME
-------------- ----------

ACCOUNTING     CLARK
ACCOUNTING     KING
ACCOUNTING     MILLER
RESEARCH       SMITH
RESEARCH       ADAMS
RESEARCH       FORD
RESEARCH       SCOTT
RESEARCH       JONES
SALES          ALLEN
SALES          BLAKE
SALES          MARTIN
SALES          JAMES
SALES          TURNER
SALES          WARD

I am not interested in the above output. I want the output to be like the one given below.

DNAME ENAME(S)

-------------- -------------------------------------------
ACCOUNTING     CLARK, KING, MILLER
RESEARCH       SMITH, ADAMS, FORD, SCOTT, JONES
SALES          ALLEN, BLAKE, MARTIN, JAMES, TURNER, WARD

I thought the following query might help. But it fails.

SQL> select b.dname, (select a.ename from emp a where a.deptno = b.deptno)
2 from dept b;
select b.dname, (select a.ename from emp a where a.deptno = b.deptno)

ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

How do I concatenate the names and make it to one big ENAMES column? Do I have to pretty much write a function that returns all the employee names for a given department and use it in the select statement as given below?

select dname, enames_fn(deptno) from dept;

Are the better alternatives?

Thanks in advance,
Logo Palanisamy

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jun 28 2000 - 00:00:00 CDT