PIVOTING date

[IanWaterman]

I have a data set which is a list, by using Case statement I can get it into this format

SELECT cl.CLIENT_KEY,
      c.cnt_name CONTACT,
      (
      CASE
        WHEN cc.cct_contacttype_cde = 2
        THEN cct_contact_details
        ELSE ''
      END) HOME_TEL,
      (
      CASE
        WHEN cc.cct_contacttype_cde = 3
        THEN cct_contact_details
        ELSE ''
      END) WORK_TEL,
      (
      CASE
        WHEN cc.cct_contacttype_cde = 4
        THEN cct_contact_details
        ELSE ''
      END) MOBILE,
      (
      CASE
        WHEN cc.cct_contacttype_cde = 6
        THEN cct_contact_details
        ELSE ''
      END) HOME_EMAIL,
      (
      CASE
        WHEN cc.cct_contacttype_cde = 7
        THEN cct_contact_details
        ELSE ''
      END) WORK_EMAIL
    FROM client cl
    INNER JOIN contact c
    ON client_key = to_number(c.cnt_internal_cde)
    INNER JOIN clientcontact cc
    ON c.contact_key = cc.cct_contact_cde 
    where cc.cct_contacttype_cde in (2, 3, 4, 6, 7)

Key      Name              Hometel   worktel   Mobile  Email                     work email
2551	Roselle Mcneill	01955604024				
2551	Roselle Mcneill		01955604024			
2551	Roselle Mcneill			 Null		
2551	Roselle Mcneill				roselle5@btinternet.com	
2551	Roselle Mcneill	                                                     Null

By using MAX and group by I can get this into a single row. This is then used as an inline query to bring back client contact details for a document. This approach is too slow.

I have now created 5 functions to find each contact detail, this is a lot faster. However, some one mentioned it might be faster to use a pivot, I have looked through old posts but can not see anything suitable.

Can any one show me how to Pivot my data. Would this be any faster than using functions?

Thanks

Ian

 

[IanWaterman]

Worked out how to do this.

Select * from 
(select cl.CLIENT_KEY,
      c.cnt_name CONTACT,
      decode(cc.cct_contacttype_cde,2,'HOME_TEL',3,'WORK_TEL',4,'MOBILE',6,'HOME_EMAIL', 7, 'WORK_EMAIL', ' ') contactref, 
      cct_contact_details
FROM client cl
    INNER JOIN contact c
    ON client_key = to_number(c.cnt_internal_cde)
    INNER JOIN clientcontact cc
    ON c.contact_key = cc.cct_contact_cde 
--where client_key = 6
)
pivot (max(cct_contact_details) for contactref in ('HOME_TEL','WORK_TEL','MOBILE','HOME_EMAIL', 'WORK_EMAIL'))

Unless there is a better way, will test to see if faster than functions.

Ian

 

[IanWaterman]

Of course this is an Oracle 10g forum so Pivot does not work!!!

Forgot my development area is 11g but production is still on 10g. Don't ask me why!!

Hey ho

Back to the drawing board. Looks like its functions then.

Ian