DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: can we generate a matrix report in oracle?

  1. #1
    Join Date
    Feb 2001
    Posts
    4

    Exclamation

    Hello there,

    can any one tell me whether we can generate a matrix report in oracle. I have some values in a table and I want a report in which these (rows) values will appear as columns

    Here is the table structure.


    Name Null? Type
    ------------------------------- -------- ----
    LOOMNO VARCHAR2(10)
    SHIFTNO VARCHAR2(10)
    DATES DATE
    REASON NUMBER(10)
    TIMES NUMBER(10)
    MM NUMBER(2)
    YYYY NUMBER(4)
    PERSON_MODIFIED VARCHAR2(50)
    DATE_MODIFIED DATE

    I want a report like :


    loom no Reason1 reason2 reaon3
    1 ******

    2

    3


    ----------------------------------------

    in the above report reason1,2,3 are the values stored in column 'reason' and '******' is the sum of times for each reason value i.e. for reason1,2,3 etc.


    thanks in advance

    regards,

    Anand
    Anand Halkunde

  2. #2
    Join Date
    Mar 2001
    Posts
    32
    I think you need to look at function called decode.This will
    do matrix kind of report.

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828

    Red face

    i did try very hard for an answer to your question if there are only three reasons only then we can do a matrix report in oracle or for that matter any dataabase using decode statement else it is not possible to do.( or perhaphs some brave soul here should attempt the feat)

  4. #4
    Join Date
    Mar 2001
    Posts
    4
    I guess we can generate the matrix report with DECODE even if the fields needed are more than 3

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828

    Cool

    manas could you show it to me then coz if it is some definite number then its easy we can do something like

    select loomno,decode (reason1 ,nvl(reason1,0),value..................................)
    group by blah

    but with thousands of reasons one has to go on writing reason 1........................reason 1000

    if you have a better way why not post the solution

  6. #6
    Join Date
    Mar 2001
    Location
    Cologne, Germany
    Posts
    24
    Hi !

    I think you can do it if you know the maximum number of rows. I tried an example giving 1 to a maximum of 5 cols.
    (There may be a more elegant way, but it works.)

    Commit;
    6502

    ----- cut here ----------------
    select decode(anzahl.zahl
    ,1, sg1.id
    ,2, sg1.id
    ,3, sg1.id
    ,4, sg1.id
    ,5, sg1.id
    ,NULL
    ) sg1
    , decode(anzahl.zahl
    ,2, sg2.id
    ,3, sg2.id
    ,4, sg2.id
    ,5, sg2.id
    ,NULL
    ) sg2
    , decode(anzahl.zahl
    ,3, sg3.id
    ,4, sg3.id
    ,5, sg3.id
    ,999999
    ) sg3
    , decode(anzahl.zahl
    ,4, sg4.id
    ,5, sg4.id
    ,NULL
    ) sg4

    , decode(anzahl.zahl
    ,5, sg5.id
    ,NULL
    ) sg5

    , anzahl.zahl

    from (select * from servicegroup where is_top='Y') sg1
    , (select * from servicegroup where is_top='Y') sg2
    , (select * from servicegroup where is_top='Y') sg3
    , (select * from servicegroup where is_top='Y') sg4
    , (select * from servicegroup where is_top='Y') sg5
    , (select count(*) zahl from servicegroup where is_top = 'Y') anzahl

    where (sg1.id < sg2.id or anzahl.zahl < 2)
    and (sg2.id < sg3.id or anzahl.zahl < 3)
    and (sg3.id < sg4.id or anzahl.zahl < 4)
    and (sg4.id < sg5.id or anzahl.zahl < 5)

    ----- cut here ----------------


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