PIVOT using two columns

by Granville Bonyata on May 20, 2012

One of the new features in 11g is PIVOT. I was reading this excellent writeup recently and saw that PIVOT could work with multiple columns, so it came to mind when a developer mentioned needing a query that would take up to the 3 newest phone numbers from the phone number table and convert them into two columns each (type of phone #, and phone #), something like this:


Name        Phone1   Phone1Type Phone2   Phone2Type Phone3   Phone3Type
Bob Wilson  555-1999 Cell       555-7890 Home       555-4321 Work

This seems like a nice case for PIVOT. Even though PIVOT was designed to work on aggregates, it handles single rows just fine when combined with analytics:

CREATE TABLE phone 
(Name         VARCHAR2(50),
 PhoneNumber  VARCHAR2(20),
 PhoneType    VARCHAR2(20),
 CreateDate   DATE)
/

INSERT INTO phone VALUES ('Bob Smith','727-555-1001','WORK',SYSDATE);
INSERT INTO phone VALUES ('Bob Smith','727-555-1002','HOME',SYSDATE-1);
INSERT INTO phone VALUES ('Bob Smith','727-555-1003','CELL',SYSDATE-2);
INSERT INTO phone VALUES ('Sam Jones','813-555-2001','WORK',SYSDATE);
INSERT INTO phone VALUES ('Sam Jones','813-555-2001','HOME',SYSDATE-1);
INSERT INTO phone VALUES ('Sam Jones','813-555-2003','CELL',SYSDATE-2);
INSERT INTO phone VALUES ('Sam Jones','813-555-2004','CELL',SYSDATE-3);
INSERT INTO phone VALUES ('Jay Lee','850-555-3001','WORK',SYSDATE);
INSERT INTO phone VALUES ('Jay Lee','850-555-3001','CELL',SYSDATE-1);

SELECT *
  FROM 
       (SELECT Name, PhoneNumber, PhoneType, my_rownumber
          FROM 
       (SELECT Name, PhoneNumber, PhoneType,
               ROW_NUMBER() OVER (PARTITION BY Name ORDER BY CreateDate DESC) my_rownumber
          FROM phone)
WHERE my_rownumber <= 3--We only want 3 newest numbers. ORDER BY Name, my_rownumber ) PIVOT (MAX(PhoneNumber) as PN, MAX(PhoneType) as Type FOR my_rownumber IN(1 AS Ph_1,                                                           2 AS Ph_2,                                                           3 AS Ph_3)); NAME       PH_1_PN       PH_1_TYPE  PH_2_PN       PH_2_TYPE  PH_3_PN      PH_3_TYPE  ---------- ------------- ---------- ------------- ---------- ------------ ---------- Bob Smith  727-555-1001  WORK       727-555-1002  HOME       727-555-1003 CELL       Jay Lee    850-555-3001  WORK       850-555-3001  CELL                               Sam Jones  813-555-2001  WORK       813-555-2001  HOME       813-555-2003 CELL      

Previous post:

Next post: