Matrix Query

1. Junior Member
Join Date
Aug 2001
Posts
15
Hello all,

I need some matrix query as follows, I have table with three columns as c_name, month, po_no. I want to generate a report which looks as followos.

C_NAME JAN FEB MAR APR .........
-----------------------------------------------------------------------
jkinfotech 30 0 6 61 ..........
softtech 6 30 29 11 ..........
allsoft 76 23 81 8 ..........

where the numbers are po_no.

Can any one help me.

Nilopher.

2. Junior Member
Join Date
Sep 2001
Location
India
Posts
1

select c_name,
sum(decode(month,'JAN',po_no,0)),
sum(decode(month,'FEB',po_no,0)),
.
.
.
sum(decode(month,'DEC',po_no,0))
from mytable
group by c_name

Join Date
Jun 2001
Location
Helsinki. Finland
Posts
3,938
Since you mentioned the word MATRIX, here is a hard one in PL/SQL:

Make a function which accepts as IN parameter a Matrix (n x n) and returns the value of the corresponding determinant.

For example:

|1 2|
|3 4|

select DET(1,2;3,4) from dual; Answer -2.

|.1 2 3|
|5 -1 0|
|6 2 81|

select DET('.1,2,3;5,-1,0;6,2,81') from dual;

In the IN string rows are to be separated by ; and column values by ,

You don't know in advance if the matrix is 3x3 ot 7x7!

4. Junior Member
Join Date
Apr 2001
Posts
47
Hi,

The best Oracle tool to create a matrix report is Oracle Report Builder. If you do not have any expirience with it, for your case you can try a query like this one:

select
c_name,
sum(decode(mon, 'Jan', pono, 0)) jan,
sum(decode(mon, 'Feb', pono, 0)) feb,
.....
.....
sum(decode(mon, 'Nov', pono, 0)) nov,
sum(decode(mon, 'Dec', pono, 0)) dec,
sum(decode(mon, 'Mon_Total', pono, 0)) mtotal
from (
select decode(grouping(c_name), 1, 'Total', c_name) c_name,
decode(grouping(month), 0, month, 'Mon_Total') mon,
sum(po_no) pono
from t
group by cube(c_name, month)
)
group by c_name

And the result may look like:

C_NAME JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC MON_TOTAL
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------
Jim 1 2 3 4 5 6 7 8 9 10 11 12 78
John 10 20 30 40 50 60 70 80 90 100 110 120 780
Total 11 22 33 44 55 66 77 88 99 110 121 132 858

Hope this helps

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•