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.

Real Time Monitoring / Tracing

I have to say , DBMS_APPLICATION_INFO is one of the under utilized package from Oracle . This is very valuable in tracing , identifying the long running operations , offending sessions ,  if you will .

This can  be valuable

a)  in multi-tier applications , where you need to  trace for a particualr module using DBMS_MONITOR
b) long running operations

SQL> show user



USER is "DBADMIN"


SQL> exec DBMS_APPLICATION_INFO.set_module(module_name => 'Demoing App Info' ,action_name => 'Demonstration');
 
SQL> col username format a14
SQL> col module format a25
SQL> col action format a35
 
SQL> Select username , module , action from GV$SESSion where username ='DBADMIN';
USERNAME MODULE ACTION
-------------- ------------------------- -----------------------------------
DBADMIN Demoing App Info Demonstration

As you can see , it is immediately visible in v$session . You need NOT commit to see the changes.

Let us say , you are going long running operations ( in a loop )  , you can set the action to be indicative of the counter . As you see , the value of module gets carried over , if the following syntax is used.

SQL> exec DBMS_APPLICATION_INFO.set_action(action_name => 'Processing 1 of 100');


PL/SQL procedure successfully completed.

SQL> Select username , module , action from GV$SESSion where username ='DBADMIN';

USERNAME MODULE ACTION
-------------- ------------------------- ----------------------------------
DBADMIN Demoing App Info Processing 1 of 100



This property  can be also set via Java .
 
metrics = new String
OracleConnection.END_TO_END_STATE_INDEX_MAX];
metrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "Zahir M";
metrics[OracleConnection.END_TO_END_MODULE_INDEX] = "Hello.java"; metrics[OracleConnection.END_TO_END_ACTION_INDEX] =
 "testing Hello ";
((OracleConnection)connection).setEndToEndMetrics(metrics, (short)0);