title: Oracle SQL Developer: How to transpose rows to columns using PIVOT function
body: <p>I'm attempting to create a query to transpose rows into columns using the PIVOT function.</p>
  
  <p>This is the <code>contact</code> table I want to transpose into rows:</p>
  
  <pre><code>   PARTYID CONTACTTEXT  CONTACTTYPECD
  ---------- ------------ -------------
         100 0354441010               1
         100 0355551010               2
         100 0428105789               3
         100 abc@home.com             4
  </code></pre>
  
  <p>My intended result:</p>
  
  <pre><code>   PARTYID PHONE        FAX          MOBILE       EMAIL      
  ---------- ------------ ------------ ------------ ------------
         100 0354441010   0355551010   0428105789   abc@home.com
  </code></pre>
  
  <p>My query:</p>
  
  <pre><code>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)); 
  </code></pre>
  
  <p>Errors I'm getting:</p>
  
  <pre><code>Error starting at line 9 in command: 
  FOR contacttypecd in (1 Phone, 2 Fax, 3 Mobile, 4 Email)) 
  Error report: 
  Unknown Command 
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>