Matrix Query

[nilopher]

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.

[cd_kokje]

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

[julian]

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!

[dmitriy]

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