Using Partition Outer Join

Most of the time , the analyst would need a report to display the activity for a month on a daily basis.

For me , I have scripted the data import job from external system and I would like to see the recordcount on a daily basis for the past one week.

Let us see an example . Let us create a table and populate data into the table.

And here is the table structure and the data .

SQL> desc datalog
Name Null? Type
----------------------------------------- -------- ----------------------------
LOADDATE NOT NULL DATE
TABLENAME VARCHAR2(30)
ROWCOUNT NUMBER


SQL> select * from datalog;


LOADDATE TABLENAME ROWCOUNT
--------- ------------------------------ ----------
29-MAY-10 ORDER 5012577
29-MAY-10 PAYMENT 165851
30-MAY-10 ORDER 5012577
30-MAY-10 PAYMENT 5073
31-MAY-10 ORDER 5012577
31-MAY-10 PAYMENT 5073
01-JUN-10 ORDER 5003
01-JUN-10 PAYMENT 5073
02-JUN-10 ORDER 5003
02-JUN-10 PAYMENT 5073
04-JUN-10 ORDER 5003
04-JUN-10 PAYMENT 5073

12 rows selected.

Here is the SQL to display the report using Partition Outer Join to show the date , though it may not have the data ( aka rowcount for that date , 3rd June , in our example) . Also , I would like to transpose it using PIVOT so that we can have a better view ( like a cross tab report )
Here is the SQL and its output.

SQL> SELECT tablename ,
2 DAY1,
3 DAY2,
4 DAY3,
5 DAY4,
6 DAY5,
7 DAY6,
8 DAY7
9 FROM
10 (SELECT Load.Tablename ,
11 NVL(Load.RowCount, 0 ) RowCount ,
12 row_number() over ( partition BY Tablename order by listdatetime DESC ) rn
13 FROM
14 (SELECT loaddate ,
15 Tablename ,
16 Rowcount
17 FROM DataLog
18 WHERE LoadDate >= Sysdate - 7
19 ) Load PARTITION BY (Tablename)
20 RIGHT OUTER JOIN
21 (SELECT to_date(sysdate - level + 1 )listdatetime
22 FROM Dual
23 CONNECT BY Level <= 7
24 ) Tim
25 ON ( TRUNC(LoadDate ) = TRUNC(Listdatetime) )
26 ) Pivot ( MAX(Rowcount) FOR Rn IN ( 1 AS Day1 , 2 AS Day2 , 3 AS Day3 , 4 AS Day4 , 5 AS Day5 , 6 AS Day6 , 7 AS Day7 ) )
27 ORDER BY tablename
28 ;

TABLENAME DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------
ORDER 5003 0 5003 5003 5012577 5012577 5012577
PAYMENT 5073 0 5073 5073 5073 5073 165851

Good luck.

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.