Oracle SQL Developer: How to transpose rows to columns using PIVOT function

Question

I'm attempting to create a query to transpose rows into columns using the PIVOT function.

This is the contact table I want to transpose into rows:

   PARTYID CONTACTTEXT  CONTACTTYPECD
---------- ------------ -------------
       100 0354441010               1
       100 0355551010               2
       100 0428105789               3
       100 [email protected]             4

My intended result:

   PARTYID PHONE        FAX          MOBILE       EMAIL      
---------- ------------ ------------ ------------ ------------
       100 0354441010   0355551010   0428105789   [email protected]

My query:

SELECT * FROM 
  ( 
    SELECT partyId, contacttext, contacttypecd 
    FROM CONTACT 
    WHERE partyId = 100; 
  ) 
  PIVOT ( 
    MAX(contacttext) 
  FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email)); 

Errors I'm getting:

Error starting at line 9 in command: 
FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email)) 
Error report: 
Unknown Command 

The reason for my problem was because my Oracle database version (Oracle9i) did not support the PIVOT function. Here's how to do it in a different way:

SELECT PartyCD
  ,MAX(DECODE(t.contacttypecd, 1, t.contacttext)) Phone
  ,MAX(DECODE(t.contacttypecd, 2, t.contacttext)) Fax
  ,MAX(DECODE(t.contacttypecd, 3, t.contacttext)) Mobile
  ,MAX(DECODE(t.contacttypecd, 4, t.contacttext)) Email
FROM 
  (
    SELECT partyid, contacttext, contacttypecd
    FROM CONTACT
    WHERE partyid = 100
  ) t
 GROUP BY PartyID

Milson

Answer 1

You have a stray semi-colon in your statement, after:

    WHERE partyId = 100; 

Remove that to make it:

SELECT * FROM 
  ( 
    SELECT partyId, contacttext, contacttypecd 
    FROM CONTACT 
    WHERE partyId = 100
  ) 
  PIVOT ( 
    MAX(contacttext) 
  FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email));

   PARTYID PHONE        FAX          MOBILE       EMAIL      
---------- ------------ ------------ ------------ ------------
       100 0354441010   0355551010   0428105789   [email protected]

It's being seen as multiple statements; the first is incomplete because it's missing a closing parenthesis (so gets ORA-00907), the second starts with that parenthesis and gets the error you reported, and then each subsequent line gets the same error. You only seem to be looking at the last reported error - it's usually much more helpful to start with the first error, clear that, and then move onto the next if it still exists.

Gardas

I removed the semicolon after line WHERE party = 100 and now have the following error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 7 Column: 8 – Milson

Found the reason to my problem: my oracle database version is Oracle9i which does not support PIVOT function. – Milson

it's lovely yeah – Solidstate