SQL & PL/SQL

Hi guys,

can anyone please help me in writing the query

I have two tables

Agents and Agent phones but in the agent phones table for one agent id it will be displaying 4 rows because one of the column it has different phone types (office,cell,home,fax)

so instead of displaying 4 rows i used max(case.........) to convert from rows to columns

now how to join this with another table

Requirement:

Database : 11.2.0.2.0

create table AGENT_PHONE

(

  agent_id          NUMBER(20) not null,

  agent_type_code   VARCHAR2(10) not null,

  agent_type_prefix VARCHAR2(10) not null,

  phone_number      VARCHAR2(16) not null,

  phone_type_code   VARCHAR2(10) not null

)

CREATE TABLEAGENTS

(

agent_id          NUMBER(20) not null,

  agent_type_code   VARCHAR2(10) not null,

  agent_type_prefix VARCHAR2(10) not null,

  NAME              VARCHAR2(40) NOT NULL

  )

  INSERT INTO AGENT_PHONE(AGENT_ID,AGENT_TYPE_CODE,AGENT_TYPE_PREFIX,PHONE_NUMBER,PHONE_TYPE_CODE)

VALUES (29709,'RE','OFFICE','4805551436','CELL');

INSERT INTO AGENT_PHONE(AGENT_ID,AGENT_TYPE_CODE,AGENT_TYPE_PREFIX,PHONE_NUMBER,PHONE_TYPE_CODE)

VALUES (29709,'RE','OFFICE','1111111111','PHONE');

INSERT INTO AGENT_PHONE(AGENT_ID,AGENT_TYPE_CODE,AGENT_TYPE_PREFIX,PHONE_NUMBER,PHONE_TYPE_CODE)

VALUES (29709,'RE','OFFICE','2223334444','OFF');

INSERT INTO AGENT_PHONE(AGENT_ID,AGENT_TYPE_CODE,AGENT_TYPE_PREFIX,PHONE_NUMBER,PHONE_TYPE_CODE)

VALUES (29709,'RE','OFFICE','5556667788','FAX');

INSERT INTO AGENTS VALUES

(29709,'RE','OFFICE','ROB');

INSERT INTO AGENTS VALUES

(1234,'RE','OFFICE','MIKE');

SELECT * FROM AGENT_PHONES

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

SELECT * FROM AGENTS

so that is the data we have in both the table

Now i transposed from rows to columns in agent phones table so i used the below query

SELECT AP.AGENT_ID,AP.AGENT_TYPE_CODE,AP.AGENT_TYPE_PREFIX,

MAX(CASE WHEN AP.PHONE_TYPE_CODE = 'CELL' THEN AP.PHONE_NUMBER END) AS CELL,

MAX(CASE WHEN AP.PHONE_TYPE_CODE = 'OFF' THEN AP.PHONE_NUMBER END) AS OFF,

MAX(CASE WHEN AP.PHONE_TYPE_CODE = 'FAX' THEN AP.PHONE_NUMBER END) AS FAX,

MAX(CASE WHEN AP.PHONE_TYPE_CODE = 'PHONE' THEN AP.PHONE_NUMBER END) AS PHONE

FROM AGENT_PHONE AP

WHERE AP.AGENT_ID=29709

GROUP BY AP.AGENT_ID,AP.AGENT_TYPE_CODE,AP.AGENT_TYPE_PREFIX;

My question is how to join this with the agents table so that my result should be like this..

I want to display all the results in the Agent table even though they are not there in agent phones table. As you can see there is another agent id 1234 is also populated

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

currently i am running this query and i am getting the result as below

SELECT *

FROM AGENTS A

LEFT OUTER JOIN AGENT_PHONE AP

ON A.AGENT_ID=AP.AGENT_ID

AND A.AGENT_TYPE_CODE=AP.AGENT_TYPE_CODE

AND A.AGENT_TYPE_PREFIX=AP.AGENT_TYPE_PREFIX

i want agent id 29709 in one row and also 1234 agent id also to be displayed