Transposing the data

Before Oracle 11g and SQL Server 2005 , transposing the data was bit cumbersome .

Transpong the data - if we want to transpose the columns into rows .

For example , if a person has multiple phone numbers and if we want to show the phone numbers on one record ... we could use PIVOT function .

To transpose rows into columns , we would use UNPIVOT .

SQL> Create table person
2 ( personid integer ,
3 telephonenumber varchar2(50) ) ;
Table created


SQL> insert into person values ( 100015 , 2484775248);
1 row inserted

SQL> insert into person values( 100015 , 9085014258);
1 row inserted

SQL> insert into person values( 100015 , 2015579964);
1 row inserted


SQL> insert into person values( 100010 , 7328225687);
1 row inserted


SQL> insert into person values( 100010 , 5328225687);


1 row inserted

Before 11g ...

SELECT personid ,
MAX(DECODE( rn ,1 , telephonenumber)) Phone_1 ,
MAX(DECODE( rn ,2 , telephonenumber)) Phone_2 ,
MAX(DECODE( rn ,3 , telephonenumber) ) Phone_3
FROM
(SELECT personid ,
telephonenumber ,
row_number() over ( partition BY personid order by personid) rn
FROM person
WHERE personid IN (100015 , 100010)
)
GROUP BY personid
 
 
In Oracle 11g / SQL Server 2005 .
 
SQL> SELECT *

2 FROM (SELECT personid , telephonenumber , row_number() over ( partition by personid order by personid) rn
3 FROM person
4 WHERE personid IN (100015 , 100010
5 ) )pivot(max(telephonenumber) for rn IN (1 ,2 , 3 ))
6 /




PERSONID 1 2 3


--------------------------------------- -------------------------

100010 7328225687 5328225687


100015 9085014258 2484775248 2015579964


In the above SQL , we know the maximum number of the columns to pivoted before hand.
If the number of columns ( to be pivoted) is dynamic , you would use PIVOT ... for XML.