sql query issue

[syncdba]

Hi,
I have a table with output as :
Emp_No ID Fname
emplo000000000004005 class000000000001333 Michael
persn000000000010368 class000000000001333 Philippe

I'm trying to write a sql which will give output as
ID Fname
class000000000001333 Michael ; Philippe

Thanks in advance

 

[SantaMufasa]

Sync,

My first impulse, whenever I must string together row values into a single column, is to use a user-defined function. Following are 1) some sample data from your model, 2) a user-defined function to string together the employees, and 3) a query that invokes the function and displays the results that you requested:

SQL> select * from syncdba;

EMP_NO                         ID                   FNAME
------------------------------ -------------------- --------
emplo000000000004005           class000000000001333 Michael
persn000000000010368           class000000000001333 Philippe
persn000000000010368           class000000000001333 Dave
persn000000000010368           class000000000001355 Barb
persn000000000010368           class000000000001355 Bill

create or replace function get_emps (class_in varchar2) return varchar2 is
    emps varchar2(4000);
begin
    for x in (select fname from syncdba where id = class_in) loop
        emps := emps||' ; '||x.fname;
    end loop;
    return ltrim(emps,' ; ');
end;
/
col id format a20
col fname format a50
select distinct id, get_emps(id) Fname
  from syncdba
 order by id;

ID                   FNAME
-------------------- -------------------------
class000000000001333 Michael ; Philippe ; Dave
class000000000001355 Barb ; Bill

Let us know if this meets your needs.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[syncdba]

Hi,
after running craeting the function & running SQL Query I'm getting
ID Fname
emplyo0000405 Michael;Michael;Michael;Michael
pers00006787 Philippe;philippe

Input Data:
ID Emp_ID Fname Lname
class000000001333 emplo000000004005 Michael Murrey
class000000001333 pers0000000008790 Philippe kil
class000000001009 emplo000000007867 Jack Mike

I'm trying for the output as:
ID Name
class000000001333 Michael Murray;Philippe Kil
class000000001009 Jack Mike

Thanks in advance.

 

[SantaMufasa]

Syncdba,

My Crystal Ball is very cloudy right now. (We have a low pressure front of Northern Utah.) So I cannot tell you what is wrong with your code without seeing it. Could you please post it for me to peruse?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]