oracle query

Hi I have requirement as follows

EmpID      Emp Last Name      Emp First Name      Emp Location      CCS Location Name      Supervision Type      Officer Name
 13                   john                         smith                         Hallifax                   chicago                            suptype1                 test1
 13                   john                         smith                         Hallifax                   chicago                           suptype2                      test2

in the above output one employee (13) has 2 officers with 2 types of supervision role.
i want the output as a single row for employee 13 with supervisor 1 and supervisor2 . can you please advise.
i am getting the output from emp,supervisor,location tables(3 tables)

ID       Last Name       First Name      Location          sup Location       Supervision Type1   Supervision Type2   Officer name1  officer name2
 13              john           smith              Hallifax                   chicago          suptype1                   suptype2                 test1                         test2

Thanks
Sarma VadlamaniprogrammeranalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Try something like (I won't add all the columns.  You can do that):

select id,last_name,min(case when rn=1 then sup_type end) suptype1, min(case when rn=2 then sup_type end) suptype2 from (
        select id, last_name, sup_type, row_number() over(partition by id order by id,sup_type) rn
       from your_table
)
group by id,last_name
Mark GeerlingsDatabase AdministratorCommented:
Or this:

select e.empid ID, e.emp_last_name LastName, e.emp_first_name FirstName,
l.emp_location Location, s1.sup_location sup_Location,
s1.Supervision_Type Supervision_Type1, s2.Supervision_Type Supervision_Type2,
s1.Officer_name Officer_name1, s2.Officer_name Officer_name2
from emp e, location l, supervisor s1, supervisor s2
where l.empid = e.empid
and s1.empid = e.emp_id
and s2.empid = e.emp_id;

Notes:
1. I had to guess a bit at your column names.  You may need to adjust these a bit for your actual column names.
2. If some employees have only one supervisor and you want them to be included, you will have to make at least the last join to supervisor an outer join like this:

and s2.empid (+) = e.emp_id;
awking00Information Technology SpecialistCommented:
Will there always be only 1 or 2 officers per employee or could there be 3 or more?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Sarma VadlamaniprogrammeranalystAuthor Commented:
mostly 2 or maximum 3 but generally only 2 supervisors.
slightwv (䄆 Netminder) Commented:
You need to know the maximum number of columns returned when the query is parsed.

So if the maximum is 3, just add a new MIN with rn=3 to mine or another join to Marks.
Sarma VadlamaniprogrammeranalystAuthor Commented:
hello with the above queries i am not getting correct result however, i could able to get the output using listagg function

LISTAGG(emp.mgrname,',')
WITHIN GROUP(ORDER BY empno asc)
over(partition BY empno)AS  OFFICER_NAME

but problem is it is repeating the names for all rows separated by comma
as follows

(test1,test2,test1,test2,test1,test2) but i want to restrict to first unique combination i.e, test1,test2 and rest should be truncated. can you please advise the way.

thanks very much
slightwv (䄆 Netminder) Commented:
listagg creates a list as a single column.  Your requirement was for different columns.

Anyway, try something like:
select ... LISTAGG(emp.mgrname,',')
 WITHIN GROUP(ORDER BY empno asc)
 over(partition BY empno)AS  OFFICER_NAME
from (
select distinct ...
from some_table
)
Mark GeerlingsDatabase AdministratorCommented:
"not getting correct result"

What result(s) did  you get?  I would expect either slightwv's suggestion or mine would give you what we understand you asked for.
slightwv (䄆 Netminder) Commented:
Here is a simplified test case with mine working.

All you should have to do is replace my inner select from tab1 with your 3 table join.

If you still cannot get it working, please provide sample data and expected results for all 3 tables.

drop table tab1 purge;
create table tab1( EmpID number, Last_Name varchar2(10), First_Name varchar2(10), Location varchar2(10), Location_Name varchar2(10), Supervision_Type varchar2(10), Officer_Name varchar2(10));

insert into tab1 values(13,'john','smith','Hallifax','chicago','suptype1','test1');
insert into tab1 values(13,'john','smith','Hallifax','chicago','suptype2','test2');
commit;

select empid,last_name,
	min(case when rn=1 then supervision_type end) suptype1,
	min(case when rn=2 then supervision_type end) suptype2,
	min(case when rn=3 then supervision_type end) suptype3
from (
         select empid, last_name, supervision_type, row_number() over(partition by empid order by empid,supervision_type) rn
        from tab1
 )
group by empid, last_name
/

Open in new window


Results from test:
     EMPID LAST_NAME  SUPTYPE1   SUPTYPE2   SUPTYPE3
---------- ---------- ---------- ---------- ----------
        13 john       suptype1   suptype2

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sarma VadlamaniprogrammeranalystAuthor Commented:
i tired with your example and it is working fine thanks very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.