Create a view with one row per ID 1

[goranm]

Hi!

My question is if it is possible to retrieve just one row for the following case.

We have a table, PARTY, where we have a single row for each PARTYID´s contact.

The table look like this:
PARTYID,
CONTACT_ID,
NAME,
PHONE

One PARTYID can have up to three different CONTACT_ID´s.

Is it possible to create a view that return just one single row for each PARTY_ID?
(instead of the view I have created below which gives me three separate rows).
Or is the only way to achieve that to to create another view which uses a group by clause on the first view?

The row I want could look something like:

PARTYID,
CONTACT1_NAME,
CONTACT1_PHONE,
CONTACT2_NAME,
CONTACT2_PHONE,
CONTACT3_NAME,
CONTACT3_PHONE

Here is the code for the view I have created:

CREATE OR REPLACE VIEW PARTY_V
(
   PARTYID,
   CONTACT1_NAME,
   CONTACT1_PHONE,
   CONTACT2_NAME,
   CONTACT2_PHONE,
   CONTACT3_NAME,
   CONTACT3_PHONE
)
AS SELECT 
   PARTYID,
   NAME,
   PHONE
   NULL,
   NULL,
   NULL,
   NULL
FROM 
   PARTY
WHERE
   CONTACT_ID = 'CONTACT1'
UNION
SELECT    
   PARTYID,
   NULL,
   NULL,
   NAME,
   PHONE
   NULL,
   NULL
FROM 
   PARTY
WHERE
   CONTACT_ID = 'CONTACT2'
UNION
SELECT 
   PARTYID,
   NULL,
   NULL,
   NULL,
   NULL,
   NAME,
   PHONE
FROM 
   PARTY
WHERE
   CONTACT_ID = 'CONTACT3'
/

/Goran

 

[Dagon]

 SELECT 
   PARTYID,
   NAME,
   PHONE
   NULL,
   NULL,
   NULL,
   NULL
FROM 
   PARTY
WHERE
   CONTACT_ID = 
(select max(p2.contact_id) 
from party p2 
where p1.partiyid = p2.partyid)

Or use RANK() over partition by (party_id order by contact_id).

 

[goranm]

Hi!

Thank you Dagon, but maybe I didn´t describe my problem so well.
I did try your sql, but it only gave me one row with one of three CONTACT_ID´s.

You see, what I want is an sql where I can get all CONTACT_ID´s for one PARTYID in one single row, because when I later join our order table to this new view I just want a single row for each order id, instead of two or three rows.

Hope I made myself clear.

Thanks

/Goran

 

[Dagon]

select 
partyid,
max(case when contactid = 'CONTACT1' then name end) as contact1_name,
max(case when contactid = 'CONTACT1' then phone end) as CONTACT1_PHONE,
max(case when contactid = 'CONTACT2' then name end) as  CONTACT2_NAME,
max(case when contactid = 'CONTACT2' then phone end) as  CONTACT2_PHONE,
max(case when contactid = 'CONTACT3' then name end) as  CONTACT3_NAME,
max(case when contactid = 'CONTACT3' then phone end) as  CONTACT3_PHONE
from party
group by partyID

 

[goranm]

Hi again!

That did the trick.
I didn´t thougt of the case function.

Thank you

/Goran