Matrix Query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Matrix Query

Hybrid View

  1. #1
    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.
    Thanks,

    Nilopher.

  2. #2
    Join Date
    Sep 2001
    Location
    India
    Posts
    1

    Angry Please Reply, if you are satisfied with ANSWER

    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

  3. #3
    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. #4
    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
  •  


Click Here to Expand Forum to Full Width